Greetings! I cannot get my "IF" formula to produce consistently correct results. I have tried many different tweaks to this formula, and this version comes the closest to working correctly. Thank you in advance for any assistance.
My formula is producing incorrect results:
=IF(C4*I2>750,"Call",IF(C4*I2<751,C4*G22,IF(C4*I2<501,C4*F22,IF(C4*I2<251,C4*E22,IF(C4*I2<100,C4*D22,IF(C4*I2<61,C4*C22,IF(C4*I2<41,C4*B22)))))))
My spreadsheet is attached.
The parts of your formula are in reverse order.
If C4*I2 < 41 the answer should be C4 * B22
but the first condition is
If C4*I2 < 751
Since 41 is < 751 this results in C4 * G22
Put the parts of the formula from smallest to largest, ie
IF C4*I2 < 41, etc IF C4*I2 < 61, etc up to < 751 then > 750
Regards
Special-K
Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.
Thank you for responding, Special-K!
Reversing the order is the first way that I tried the formula, and it would not work at all. I'll try again using exactly the same format and reversing, but I 99.9% positive that I have already tried that with incorrect results.
Thanks!
You should be able to shorten that to
=IF(C4*I2 > 750,"Call",C4*INDIRECT((LOOKUP(C4*I2,{1E-99,41,61,100,251,501,750},{"B22","C22","D22","E22","F22","G22",""}))))
which on second thought is just about is as long
It can be done with LOOKUP instead of IF's. Try to upload the spreadsheet.
Quang PT
Thanks again, Special-K! I haven't yet tested the entire sheet, but the reverse formula is working. I must have made some minor mistake when I tried it the first time, so I'll make sure to be more careful in the future.
I sincerely appreciate your help, and will "pass it forward" if and when I can.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks