formula in excell 2007

[Guest]

Ok, I am trying to create a formula that will do the following:

If cell F2 has any number between 23 and 28, I want it to multiply cell E2
by 20%,
if it has 28.01 and 32, then I want it to multiply cell E2 by 25%. If it has
32.01- 40, then I want cell E2 to multiply by 30%. And so on.
I only want it to do 5 sets of this.

Can anybody help?
please email me at (e-mail address removed)
Thanks,

Jorge

 

[Bob Phillips]

=E2*IF(F2>50,40%,IF(F2>40,35%,IF(F2>32,30%,IF(F2>28,25%,IF(F2>23,20%,0)))))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Roger Govier]

Hi

Your intervals don't seem to have a regular pattern

You could use
=E2*((F2>23)*20%+(F2>28)*5%+(F2>32)*5%)

Just keep adding the extra terms for each 5%, with values set at where
your intervals occur

 

[Guest]

Thank you very much. AFter trying and trying I finally got this to work. I
had to change some values from my example to get this to fit, and it did.

I do have another question:

I want excel to calcute using the formula you gave me if it is a new
product, but if it is an old product I want to use a different formula.
So if b2 says "new" then I want to use 1 formula but if b2 says "old" then I
want to use a different formula.
So on B2 I created a drop down box with "new" and "old to choose from. SO
if "new" is selected, I want the numbers to change from when "old" is
selected. Does this make sense? Is it a macro I am after?

Thanks for your help,