Issue with zero/# issue!!

  • Thread starter Thread starter drvortex
  • Start date Start date
D

drvortex

I thought I was done but some other problem came up. What I'm doing i
adding up cells to get a value x then adding another set of cells t
get value y. I want to do this calculation x/y but getting the erro
because x is zero therefore won't compute.

=(January!F47+February!F47+March!F47+April!F47+May!F82+June!F102+July!F67+August!F72+September!F62+October!F47+November!F42+December!F42)/(January!G47+February!G47+March!G47+April!G47+May!G82+June!G102+July!G67+August!G72+September!G62+October!G47+November!G42+December!G42)

This is what I have. I'm trying to figure out what I need to add t
this equation to make that happen. I think I need do an IF statemen
stating if the value is >0 then calculate, if it doesn't then put
blank. This is important because lets say JanF47 is 1 but FebF47 is 0
the computation is not allowed. Also, on the bottom side of th
division...if JanG47 is 1 but FebG47 is zero...doesn't like th
calculation. I want the calculation to ignore the zeros.

Hope you understand what I'm asking. I'm going to try several thing
to see if it will work. Thanks again for your help.

Jaso
 
One way:

=IF((January!G47+February!G47+March!G47+April!G47+May!G82+June!G102+July!
G67+August!G72+September!G62+October!G47+November!G42+December!G42)=0,"",
(January!F47+February!F47+March!F47+April!F47+May!F82+June!F102+July!F67+
August!F72+September!F62+October!F47+November!F42+December!F42)/(January!
G47+February!G47+March!G47+April!G47+May!G82+June!G102+July!G67+August!G7
2+September!G62+October!G47+November!G42+December!G42))
 
didnt work. I believe the problem is within the equation, i have cells
being empty or stating FALSE...therefore, when you add up all the cells
and one (or a few) are empty or show FALSE...it will not compute
properly. I'm trying to understand this but not working. I may have
to send the spreadsheet to someone. Its hard to explain.
 
Using SUM() rather than the addition operator (+) will ignore text and
booleans.
 
I fixed it. I didn't change the above formula but went into the other
cells and adjusted the formula. I had "" (double quotes) in the FALSE
argument instead of just putting 0 (number zero). This then adds a
zero instead of a BLANK which equals to a space but Excel doesn't
represent that as a value but as a string. Anyway, all works now.
 
Back
Top