if/and statement

[erniepoe]

Hi all,

I have the following formula, but not working. Currently, the value in G6 is 2018 and H6=P3, so i want the following formula to apply:

(P$2-$I6)/P$2*($J6/26*P$1)

unfortunately, the last formula after the final column is what Excel is finding, which is the formula to use if G6 DOES NOT = 2018.

can anybody spot what I'm doing wrong here? Thanks so much!


=IF(AND($G6="2018",$H6>P$3),0,IF(AND($G6="2018",$H6=P$3),(P$2-$I6)/P$2*($J6/26*P$1),$J6/26*P$1))

 

[Joe4]

If the entry in cell G6 is the numeric value 2018, and not the text entry of 2018, then removes the double-quotes from around the number, i.e.

[COLOR=#333333]=IF(AND($G6=2018,$H6>P$3),0,IF(AND($G6=2018,$H6=P$3),(P$2-$I6)/P$2*($J6/26*P$1),$J6/26*P$1))[/COLOR]

Note: I have not tried to analyze the reset of your formula, just noticed this potential issue sticking out like a sore thumb!

 

[philwojo]

Are you sure H6 and P3 are truly equal, depending on the data source for those cells there could be hidden decimal values that you don't see displayed in the cells but could cause those to not truly be equal.

I would put this formula in a cell and see what you get.

=h6=p3

If it gives True then you are good, if it is False, then you'd need to look further as to why.

 

[erniepoe]

If the entry in cell G6 is the numeric value 2018, and not the text entry of 2018, then removes the double-quotes from around the number, i.e.

[COLOR=#333333]=IF(AND($G6=2018,$H6>P$3),0,IF(AND($G6=2018,$H6=P$3),(P$2-$I6)/P$2*($J6/26*P$1),$J6/26*P$1))[/COLOR]

Note: I have not tried to analyze the reset of your formula, just noticed this potential issue sticking out like a sore thumb!

yes, it appears that the 2018 should not have had the quotes, as it was the number. Today has not been my day, lol. Thanks for the catch, Joe!

 

[Joe4]

No problem! Glad to help!