SUM Formula

[exceldotcom]

Hi everyone,

Got a formula here that is calculating oddly. Not sure if I am missing something.

=IF(AND(VLOOKUP(D23,D:P,11,FALSE)="Yes",G23=0),0,+Y23-Z23)

The formula is FALSE (which is correct) so the bolded section is the part pulling the calculation of the following (the calculation is subtracting YTD from the most recent month, which should be $0 for everyone currently):

Y23 = 25818.9122075294
Z23 = 25818.9122075294

For other member calculations I'm getting "$-" in the cell, which is correct. However, for this member I'm getting "$0.00". All the cells are formatted as "Accounting".

Any ideas?

Thanks!

 

[BenMiller]

Looks like G23 does not equal 0, so your formula is returning 0.

=IF(AND(VLOOKUP(D23,D:P,11,FALSE)="Yes",G23=0),0,+Y23-Z23)

 

[Joe4]

You might have the floating arithmetic situation going on, which makes Excel think there is some tiny fractional difference (see: https://www.microsoft.com/en-us/mic...y-does-excel-give-me-seemingly-wrong-answers/).

Since you are only returning two decimal places, rounding to two places should take care of that, i.e.

[COLOR=#333333]=IF(AND(VLOOKUP(D23,D:P,11,FALSE)="Yes",G23=0),0,[B]ROUND([/B][/COLOR][B]Y23-Z23,2))[/B]

You might need/want to round G23 also, just to be sure, i.e.

[COLOR=#333333]=IF(AND(VLOOKUP(D23,D:P,11,FALSE)="Yes",[B]ROUND(G23,2)[/B]=0),0,[B]ROUND([/B][/COLOR][B]Y23-Z23,2))[/B]

 

[exceldotcom]

Thank you! Adding the =ROUND in has fixed this. Still odd as per my hardcoded value above, the numbers are identical (and
receive a TRUE statement from an =EXACT formula...).