IFNA nested

[katrina bethea]

im sure this is a simple fix and i have read other articles that say to put iferror/ifna/isna at the beginning of the formula but i still can't seem to get it to work.

The two formulas are the ones in column "AK";

  =IF(AJ3<AH3,AJ3,IF(AND(AM3>=49,AH3<=44.99),AH3,IF(AND(AM3>=39,AH3<=32.99),AH3,IF(AND(AM3>=29,AH3<=24.99,AH3),AH3,IF(AND(AM3>=19,AH3<=16.99),AH3,IF(AND(AM3>=14,AH3<=9.99),AH3,"NO CHANGE"))))))                     

               (makes sure the price does not go over our max used price or our competitors)

and "AM";

=IF(AND(AI3>AL3,AI3>49.99),49.99,IF(AND(ISNUMBER(AI3),AI3<>AL3),AI3,AL3))

             (determines if the new/acquisition price has changed from our supplier.)

just a basic rundown of what i'm trying to accomplish with these two formulas is determine the new and used price based off several factors and they are currently working appropriately EXCEPT for the fact i can not get it to ignore #n/a. examples fields would be;

"AK8" should result in 44.99 (AH8)

"AK23" should result 22.99 (AH23)

"AM19" should result 19.99 (AL19)

"AM23" should result 29.99 (AL23)

attached is the sheet i'm working with so you can get a better visual. remember all other values are correct so if any of the values change besides the "#N/A" then i can not use that formula.

[#2 · Accepted solution]

the #N/A signifies that the search done found nothing..

to bypass that use IFNA or IFERROR

ex:

=IFNA(IF(AND(AI3>AL3,AI3>49.99),49.99,IF(AND(ISNUMBER(AI3),AI3<>AL3),AI3,AL3)),"")

it will return a blank or you can change that to 0 or "not found" ....

Hope this helps.

thanks..

[#3]

i knew it would be something simple. thank you. instead of putting "ifna" in the very front like that i was replacing the first "if".  Thank you i appreciate it.

[#4]

glad to help..