if then formula
 

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

if then formula
 

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" wrote in message
...
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

if then formula
 

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" wrote:

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" wrote in message
...
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

if then formula
 

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" wrote in message
...
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" wrote:

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" wrote in message
...
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

if then formula
 

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

"Peo Sjoblom" wrote:

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" wrote in message
...
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" wrote:

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" wrote in message
...
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