MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Formula


Posted by Ed on February 07, 2002 9:17 AM

I'm sure this is simple to many but I have not been
able to figure it out.


Need a Formula for this:

IF(E19<4,4.4)
IF(E19>3<7,4.85)
IF(E19>6<13,5.55)
IF(E19>12<20,6.70)
IF(E19>19<40*.33)
If(E19>39<60*.29)
IF(E19>59<100*.27)
Thanks,
Ed


Posted by Brian on February 07, 2002 1:17 PM

What exactly are you trying to do Ed? You have a couple of holes in your logic. What outcome for 3.5, which is greater than 3 and less than 4? And what exactly are the *'s meant to represent in this case?

Posted by Ed on February 07, 2002 2:42 PM

Brian:
The numbers will all be whole numbers.
* is multiply by the .26 or whatever the given amount is.
I am making an order form where the supplier charges a different amount for each set of weights. e.g. up to 3 lbs = $4.40, 4-6 lbs $4.85
Any help would be appriciated.
Ed

Posted by Brian on February 08, 2002 2:04 PM

This should do it ED,if I understand you correctly.

=IF(E19<=3,4.4,IF(E19<=6,4.85,IF(E19<=13,5.55,IF(E19<=20,6.7,IF(E19<=40,E19*0.33,IF(E19<=60,E19*0.29,IF(E19<=100,E19*0.27,"Above 100")))))))

Unfortunately with nested IF's you can only go as far as 7 so if you want to make any more allowances than you have there you are stuck.

Posted by Ed on February 08, 2002 6:46 PM

Thanks for the help.
Ed