| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple if senerio
Hello,
I am tried to work out a compensation calculator with accelerators and am having issues. 100% of quota is $1,000 Range is: <49% (0%) 50%-69% (50%) 70%-89% (70%) 90%-109% (100%) 110%-129% (130%) 130%-149% (140%) 150%< (160%) So if the result is 55% of quota is reached then take (1000*55%) then multiply that by the decelerator of 50%; but if 112% is reached then take (100*112%) then multiply that be the accelerator of 130%. All of this needs to be in one cell. This is what I have so far, but it isnt working =IF(I16<49%,((B28*I16)*H28),IF(69%I1650%,((B29*I 16)*H29),IF(70%<I16<89%,((B30*I16)*H30),IF(90%<I16 <109%,((B31*I16)*H31),)))) Thank you, Ray |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple if senerio
=A1*1000*LOOKUP(A1,{0,0.5,0.7,0.9,1.1,1.3,1.5},{0, 0.5,0.7,1,1.3,1.4,1.6})
-- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Ray" wrote in message ... Hello, I am tried to work out a compensation calculator with accelerators and am having issues. 100% of quota is $1,000 Range is: <49% (0%) 50%-69% (50%) 70%-89% (70%) 90%-109% (100%) 110%-129% (130%) 130%-149% (140%) 150%< (160%) So if the result is 55% of quota is reached then take (1000*55%) then multiply that by the decelerator of 50%; but if 112% is reached then take (100*112%) then multiply that be the accelerator of 130%. All of this needs to be in one cell. This is what I have so far, but it isn't working =IF(I16<49%,((B28*I16)*H28),IF(69%I1650%,((B29*I 16)*H29),IF(70%<I16<89%,(( B30*I16)*H30),IF(90%<I16<109%,((B31*I16)*H31),)))) Thank you, Ray |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple if senerio
Reformat your table like this
A B C 1 Range is: 1000 55% 2 0% 275 3 50% 50% 4 70% 70% 5 90% 100% 6 110% 130% 7 130% 140% 8 150% 160% Formula in C2 gives you the result: =$B$1*$C$1*IF(ISERROR(VLOOKUP($C$1,$A$2:$B$8,2)),0 ,VLOOKUP($C$1,$A$2:$B$8,2)) Regards, Stefi €˛Ray€¯ ezt Ć*rta: Hello, I am tried to work out a compensation calculator with accelerators and am having issues. 100% of quota is $1,000 Range is: <49% (0%) 50%-69% (50%) 70%-89% (70%) 90%-109% (100%) 110%-129% (130%) 130%-149% (140%) 150%< (160%) So if the result is 55% of quota is reached then take (1000*55%) then multiply that by the decelerator of 50%; but if 112% is reached then take (100*112%) then multiply that be the accelerator of 130%. All of this needs to be in one cell. This is what I have so far, but it isnt working =IF(I16<49%,((B28*I16)*H28),IF(69%I1650%,((B29*I 16)*H29),IF(70%<I16<89%,((B30*I16)*H30),IF(90%<I16 <109%,((B31*I16)*H31),)))) Thank you, Ray |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple if senerio
So far both these two examples haven't helped. Is theremanything else that
can be done? "Ray" wrote: Hello, I am tried to work out a compensation calculator with accelerators and am having issues. 100% of quota is $1,000 Range is: <49% (0%) 50%-69% (50%) 70%-89% (70%) 90%-109% (100%) 110%-129% (130%) 130%-149% (140%) 150%< (160%) So if the result is 55% of quota is reached then take (1000*55%) then multiply that by the decelerator of 50%; but if 112% is reached then take (100*112%) then multiply that be the accelerator of 130%. All of this needs to be in one cell. This is what I have so far, but it isnt working =IF(I16<49%,((B28*I16)*H28),IF(69%I1650%,((B29*I 16)*H29),IF(70%<I16<89%,((B30*I16)*H30),IF(90%<I16 <109%,((B31*I16)*H31),)))) Thank you, Ray |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple if senerio
try this amigo....
place the blank quota in cell A1 : e.g. $1000. PLACE THE ACCELERATOR FORMULA AS...ONE CELL AS U WISH.... =if(A1=150%,160%,IF(A1=130%,140%,IF(A1=110%,130 %,IF(A1=90%,100%,IF(A1=70%,70%,IF(A1=50%,50%,0% )))))) REPLACE THE VALUES WITH CELL REFERENCES.... -- "Bright minds are blessed to those who share them.."-rsb. "Ray" wrote: Hello, I am tried to work out a compensation calculator with accelerators and am having issues. 100% of quota is $1,000 Range is: <49% (0%) 50%-69% (50%) 70%-89% (70%) 90%-109% (100%) 110%-129% (130%) 130%-149% (140%) 150%< (160%) So if the result is 55% of quota is reached then take (1000*55%) then multiply that by the decelerator of 50%; but if 112% is reached then take (100*112%) then multiply that be the accelerator of 130%. All of this needs to be in one cell. This is what I have so far, but it isnt working =IF(I16<49%,((B28*I16)*H28),IF(69%I1650%,((B29*I 16)*H29),IF(70%<I16<89%,((B30*I16)*H30),IF(90%<I16 <109%,((B31*I16)*H31),)))) Thank you, Ray |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple if senerio
IT WORKED!!!! Still don't see why but it does!!!!
THANK YOU ROMELSB!!! "romelsb" wrote: try this amigo.... place the blank quota in cell A1 : e.g. $1000. PLACE THE ACCELERATOR FORMULA AS...ONE CELL AS U WISH.... =if(A1=150%,160%,IF(A1=130%,140%,IF(A1=110%,130 %,IF(A1=90%,100%,IF(A1=70%,70%,IF(A1=50%,50%,0% )))))) REPLACE THE VALUES WITH CELL REFERENCES.... -- "Bright minds are blessed to those who share them.."-rsb. "Ray" wrote: Hello, I am tried to work out a compensation calculator with accelerators and am having issues. 100% of quota is $1,000 Range is: <49% (0%) 50%-69% (50%) 70%-89% (70%) 90%-109% (100%) 110%-129% (130%) 130%-149% (140%) 150%< (160%) So if the result is 55% of quota is reached then take (1000*55%) then multiply that by the decelerator of 50%; but if 112% is reached then take (100*112%) then multiply that be the accelerator of 130%. All of this needs to be in one cell. This is what I have so far, but it isnt working =IF(I16<49%,((B28*I16)*H28),IF(69%I1650%,((B29*I 16)*H29),IF(70%<I16<89%,((B30*I16)*H30),IF(90%<I16 <109%,((B31*I16)*H31),)))) Thank you, Ray |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple if senerio
til next thread...thanks for trying....more power....
-- "Bright minds are blessed to those who share them.."-rsb. "Ray" wrote: IT WORKED!!!! Still don't see why but it does!!!! THANK YOU ROMELSB!!! "romelsb" wrote: try this amigo.... place the blank quota in cell A1 : e.g. $1000. PLACE THE ACCELERATOR FORMULA AS...ONE CELL AS U WISH.... =if(A1=150%,160%,IF(A1=130%,140%,IF(A1=110%,130 %,IF(A1=90%,100%,IF(A1=70%,70%,IF(A1=50%,50%,0% )))))) REPLACE THE VALUES WITH CELL REFERENCES.... -- "Bright minds are blessed to those who share them.."-rsb. "Ray" wrote: Hello, I am tried to work out a compensation calculator with accelerators and am having issues. 100% of quota is $1,000 Range is: <49% (0%) 50%-69% (50%) 70%-89% (70%) 90%-109% (100%) 110%-129% (130%) 130%-149% (140%) 150%< (160%) So if the result is 55% of quota is reached then take (1000*55%) then multiply that by the decelerator of 50%; but if 112% is reached then take (100*112%) then multiply that be the accelerator of 130%. All of this needs to be in one cell. This is what I have so far, but it isnt working =IF(I16<49%,((B28*I16)*H28),IF(69%I1650%,((B29*I 16)*H29),IF(70%<I16<89%,((B30*I16)*H30),IF(90%<I16 <109%,((B31*I16)*H31),)))) Thank you, Ray |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Display multiple lines of text within a cell from multiple column. | Excel Worksheet Functions | |||
| work with multiple workbooks on separate monitor for 2003 edition | Excel Worksheet Functions | |||
| Number of unique attributes that multiple people have | Excel Discussion (Misc queries) | |||
| How do i auto create multiple files from 1 with multiple sheets | Excel Worksheet Functions | |||
| Adding multiple worksheets | Excel Worksheet Functions | |||
Linear Mode
