if then formula

[kdw]

I am trying to come up with a formula where if a value is this then the
result should be this. ex
if A1 is between 0 - 49 then the result should be 75
if a1 is between 50 - 99 then the result should be 150
if a1 is over 100 then the result should be 125% of a1 rounded to the
nearest 25.

I thought that maybe I should separate to above and below 100 and then do
separate formulas but for some reason =lookup(a1,{0,50;75,150}) is not
working for the smaller group and the formula for the second group is totally
baffling me.

Any help out there??

thank you in advance

 

[Peo Sjoblom]

One way

=IF(A1>=100,ROUND((1.25*A1)/25,0)*25,IF(A1<50,75,150))

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon

 

[kdw]

when I put this into my worksheet and A1 was 100 then it gave me a result of
15000%. What I would have wanted it to show was 125. This is for a mass
mailing for donations. So if the donors gave between 0-49$ last year then I
would want $50 $75 $100 to print out on their response card for them
to check one off. If they had donated $60 last year then I would want $100
$150 $250 to print out and if they had donated $200 then I would want
$200(100%) $250(125%) and $300(150%) to print out on their card. I would
use 3 different formulas for 3 separate columns

A1 A2 A3 A4
last years 1ST ASK AMT 2ND ASK AMT 3RD ASK AMT
$22 50 75 100
65 100 150 250
120 125 150 175
230 225 275 350

=LOOKUP(A1,{0,50;50,100}) this worked for the 1st ask amt (a1) but it keeps
giving me a 50$ return when I put =lookup(a1,(0,50;75,150}) for column a3.

Also I would need to include in this formula or another formula for anything
over $100. If they donated $120 last year then I would want 100% rounded to
the nearest $25 for a2 answer being $125, 125% rounded to nearest 25 for a3
being $150 and 150% rounded to nearest 25 for a4 being $175.00(180).

 

[Peo Sjoblom]

It can't, because if your value in A1 is 100 it will return 125 and that is
125% of A1 rounded to nearest 25?
Given the conditions you gave in your first post

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon

 

[kdw]

Thank you so much. I went back and checked my entries and I had copied
something wrong. Your idea worked perfectly.