Solved Excel IF statment - Need help

Microsoft Excel for mac 2011 - macintosh
August 30, 2011 at 17:24:18
Specs: N/A
I can't figure out how to combine these to formulas. I know they are not correct, just can't figure it out. I know I need to remove the IF(F3>=E10,"0" and IF(F3<=D11,"0". But this is how I can get the two to work separately.



What I'm trying to do is:
See data
if F3 is between 0-30,000, multiply by $1.
If F3 is between 30,001-75,000, multiply ((F3-30,001)*.8)+$30,000.
If F3 is between 75,001-120,000, multiply ((F3-75,001)*.5)+$30,000+$35,999.
If F3 is greater the 120,001 the multiply ((F3-120,001)*.4)+30,000+35,999,+$22,499.

I'm trying to compound the $ as it achieves the different levels. So, if we hit 120,000 units it will add $30,000+35,999+22,499=88,498.

If we hit 80,000 units it should be $30,000+35,999+((80,000units-75,001units)*.5)=$68,498.

F3 - (X) Total units
C9 - $1
C10 - 0
C11 - 30000
D9 - $.8
D10 - 30001
D11 - 75000
E9 - $.5
E10 - 75001
E11 - 120000
F9 - $.4
F10 - 120001


See More: Excel IF statment - Need help

Report •

August 30, 2011 at 19:20:16
✔ Best Answer
With hard numbers, I believe that this is what you are looking for:

=IF(F3<30001, F3, IF(F3<75001,(F3-35001)*0.8+30000, IF(F3<120001,(F3-75001)*0.5+65999, (F3-120001)*0.4+87498)))

With your cell references, it would look like this:

=IF(F3<D10, F3, IF(F3<E10, (F3-D10)*D9+C11, IF(F3<F10, (F3-E10)*E9+C11+35999, (F3-F10)*F9+C11+58498)))

It seems to me that as long as you have some of the hard numbers in cells, you might as well put all of them in cells so you don't have to have hard numbers in the formula. Why mix and match?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

August 31, 2011 at 09:09:41
Worked perfectly!
Thank you!
Saved me a lot of frustration.


Report •

Related Solutions

Ask Question