Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
2 Nesting questions | Excel Worksheet Functions | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) |