Microsoft Excel for mac 2011 - macintosh

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. =IF(F3>=E10,"0",IF(F3<=C11,F3*C9,IF(F3>=D10,C13+((F3-D10)*D9))))

=IF(F3<=D11,"0",IF(F3<=E11,C13+D13+((F3-E10)*E9),IF(F3>=F10,F13+((F3-F10)*F9))))

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 - 120001Anyone!!!

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

allof 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.

Worked perfectly!

Thank you!

Saved me a lot of frustration.John

Ask Your Question

Weekly Poll

Do you think Amazon should have bought Whole Foods?

Discuss in The Lounge

Poll History