IF Statement

[mdestasio]

I am about to lose my mind...I think i am correctly writing an IF statement but it is not returning the value I am looking for. Can anyone validate that I have written this correctly. Thank you!

=IF(J24=0,(I27+J23),(I27+J24))

If J24 is greater than zero it returns the correct value. If J24 is 0 it just returns 0.

 

[Seti]

What is in I27? If it is 0, then the result would be 0 when J24=0. Otherwise, the formula looks OK.

 

[mdestasio]

Thanks for the validation...I27 is 17400, i just tested this on a new tab and it works...which leads me to the next question...what else would cause it to returning a value of zero?

 

[mdestasio]

BTW I ran the formula evaluator and it checks out step by step. True statement and the first formula get the correct sum but then shows zero as the result

 

[Seti]

What do you get if you put =I27+J23 in a blank cell? Is it 0? That's what the formula is calling for.

 

[mdestasio]

hmmm...0

 

[Seti]

So maybe one is >0 and one is <0 or both are 0.

Also, note that the formula does not reference J24, but I think that is what you wanted.

 

[Scott Huish]

That formula can be reduced to:

=I27+IF(J24,J24,J23)

What is in all 3 cells?

 

[mdestasio]

What I am trying to calculate is projected revenue by week. I27 ithe cummulative actual revenue, J23 is the weekly planned revenue and J24 is weekly actual revenue. What I am asking the formula to do is calculate projected revenue projectedby taking the cummulative total (i27) and adding weekly actuals (j24), but if the actuals are currently zero then add the planned revenue (j23).

After further testing it appears my problem is with j23. If i enter =j23 in a cell it gives me 0 when the value $16000. the formula for cell j23 is: =SUMIF($G:$G,"planned",K:K)