1

I am trying to get a formula to compute when 0 is present, and the #DIV/0! error is being problematic.

I want cell H4 to calculate a ratio using cells F4 and G4. This works fine when 0 is not present in cell G4 however when 0 is present I get the #DIV/0! error.

How can this formula be modified so it still works out the ratio even if there is a 0 in cell G4?

The current formula is : =IF(COUNT(G4)=1,SUM(F4/G4),"")

Insider
  • 95
  • 12
  • For example, if there is a ```6``` in cell ```F4``` and ```G4``` has a ```0```, I want it to work out the ratio 6:0. – Insider Feb 15 '21 at 15:51
  • 1
    6/0 is undefined though. Also the `SUM` in your current formula is redundant: the `SUM` of `F4/G4` is just `F4/G4`. I'm guessing you want `=F4/IF(G4=0,1,G4)`. – BigBen Feb 15 '21 at 15:53
  • @BigBen that appears to work, is there any way you could look at another question I have posted? it is regarding VBA that is proving difficult if not then thanks for your help this time around! – Insider Feb 15 '21 at 16:01

1 Answers1

1

You could use the AGGREGATE formula. By wrapping it around your formulae you can tell AGGREGATE to ignore errors. Here I have used the sum feature so it returns the same answer. The 6 flag tells it to ignore errors:

  =AGGREGATE(9,6,IF(COUNT(G4)=1,F4/G4,""))
Dharman
  • 30,962
  • 25
  • 85
  • 135
MTwem
  • 135
  • 1
  • 1
  • 9