Forum Discussion
IFNA nested
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.