| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula based on conditions
Hello,
Me and a co-worker have been working on an issue we are trying to figure out. We have a basic formula but need it to apply different ratings based on a person's tenure. Tenure will be in Column C. I will put below what we would like to do, so if anyone has any easy suggestions, we are all ears. I say easy because we will need to replicate it about six times for different fields all based on tenure. Thank you Now if C5=6 then we would like: =IF(R5<235,5,IF(R5<=245,4,IF(R5<=255,3,IF(R5<=280, 2,IF(R5280,1) ) ) ) ) Or if C5=5 then we would like: =IF(R5<240,5,IF(R5<=250,4,IF(R5<=260,3,IF(R5<=285, 2,IF(R5285,1) ) ) ) ) or if C5=4 then we would like: =IF(R5<245,5,IF(R5<=255,4,IF(R5<=265,3,IF(R5<=290, 2,IF(R5290,1) ) ) ) ) or if C5=3 then we would like: =IF(R5<250,5,IF(R5<=260,4,IF(R5<=270,3,IF(R5<=295, 2,IF(R5295,1) ) ) ) ) or if C5=2 then we would like: =IF(R5<255,5,IF(R5<=265,4,IF(R5<=275,3,IF(R5<=300, 2,IF(R5300,1) ) ) ) ) or if C5=1 then we would like: =IF(R5<260,5,IF(R5<=270,4,IF(R5<=280,3,IF(R5<=305, 2,IF(R5305,1) ) ) ) ) Thanks again. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula based on conditions
I assume that C5 can only be integer, and can't be less than 1? [If these constraints don't apply, then the formula can be tweaked, but you'll need to specify what you want for the unspecified conditions.] =IF(R5<265-5*MIN(C5,6),5,IF(R5<=275-5*MIN(C5,6),4,IF(R5<=285-5*MIN(C5,6),3,IF(R5<=310-5*MIN(C5,6),2,1 ) ) ) ) -- David Biddulph "PT40" wrote in message ... Hello, Me and a co-worker have been working on an issue we are trying to figure out. We have a basic formula but need it to apply different ratings based on a person's tenure. Tenure will be in Column C. I will put below what we would like to do, so if anyone has any easy suggestions, we are all ears. I say easy because we will need to replicate it about six times for different fields all based on tenure. Thank you Now if C5=6 then we would like: =IF(R5<235,5,IF(R5<=245,4,IF(R5<=255,3,IF(R5<=280, 2,IF(R5280,1) ) ) ) ) Or if C5=5 then we would like: =IF(R5<240,5,IF(R5<=250,4,IF(R5<=260,3,IF(R5<=285, 2,IF(R5285,1) ) ) ) ) or if C5=4 then we would like: =IF(R5<245,5,IF(R5<=255,4,IF(R5<=265,3,IF(R5<=290, 2,IF(R5290,1) ) ) ) ) or if C5=3 then we would like: =IF(R5<250,5,IF(R5<=260,4,IF(R5<=270,3,IF(R5<=295, 2,IF(R5295,1) ) ) ) ) or if C5=2 then we would like: =IF(R5<255,5,IF(R5<=265,4,IF(R5<=275,3,IF(R5<=300, 2,IF(R5300,1) ) ) ) ) or if C5=1 then we would like: =IF(R5<260,5,IF(R5<=270,4,IF(R5<=280,3,IF(R5<=305, 2,IF(R5305,1) ) ) ) ) Thanks again. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula based on conditions
Yes, that would be correct. One would be the minimum for Column C.
Let me see if this helps. One of the other conditions we have will only be based on four months. IF C5=4 then we would like =IF(H5=36%,5,IF(H5=31%,4,IF(H5=24%,3,IF(H5=22. 5%,2,IF(H5=0,1,"N/A"))))) And if C53, =IF(H5=34%,5,IF(H5=29%,4,IF(H5=22%,3,IF(H5=20. 5%,2,IF(H5=0,1,"N/A"))))) And if C52, =IF(H5=32%,5,IF(H5=27%,4,IF(H5=20%,3,IF(H5=18. 5%,2,IF(H5=0,1,"N/A"))))) And if C51, =IF(H5=30%,5,IF(H5=25%,4,IF(H5=18%,3,IF(H5=16. 5%,2,IF(H5=0,1,"N/A"))))) Thanks for your assistance. "David Biddulph" wrote: I assume that C5 can only be integer, and can't be less than 1? [If these constraints don't apply, then the formula can be tweaked, but you'll need to specify what you want for the unspecified conditions.] =IF(R5<265-5*MIN(C5,6),5,IF(R5<=275-5*MIN(C5,6),4,IF(R5<=285-5*MIN(C5,6),3,IF(R5<=310-5*MIN(C5,6),2,1 ) ) ) ) -- David Biddulph "PT40" wrote in message ... Hello, Me and a co-worker have been working on an issue we are trying to figure out. We have a basic formula but need it to apply different ratings based on a person's tenure. Tenure will be in Column C. I will put below what we would like to do, so if anyone has any easy suggestions, we are all ears. I say easy because we will need to replicate it about six times for different fields all based on tenure. Thank you Now if C5=6 then we would like: =IF(R5<235,5,IF(R5<=245,4,IF(R5<=255,3,IF(R5<=280, 2,IF(R5280,1) ) ) ) ) Or if C5=5 then we would like: =IF(R5<240,5,IF(R5<=250,4,IF(R5<=260,3,IF(R5<=285, 2,IF(R5285,1) ) ) ) ) or if C5=4 then we would like: =IF(R5<245,5,IF(R5<=255,4,IF(R5<=265,3,IF(R5<=290, 2,IF(R5290,1) ) ) ) ) or if C5=3 then we would like: =IF(R5<250,5,IF(R5<=260,4,IF(R5<=270,3,IF(R5<=295, 2,IF(R5295,1) ) ) ) ) or if C5=2 then we would like: =IF(R5<255,5,IF(R5<=265,4,IF(R5<=275,3,IF(R5<=300, 2,IF(R5300,1) ) ) ) ) or if C5=1 then we would like: =IF(R5<260,5,IF(R5<=270,4,IF(R5<=280,3,IF(R5<=305, 2,IF(R5305,1) ) ) ) ) Thanks again. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula based on conditions
Rather, if you wouldn't mind explaining how that formula you gave me worked.
I am at a loss, but it does seem to work. Thanks so much. "PT40" wrote: Yes, that would be correct. One would be the minimum for Column C. Let me see if this helps. One of the other conditions we have will only be based on four months. IF C5=4 then we would like =IF(H5=36%,5,IF(H5=31%,4,IF(H5=24%,3,IF(H5=22. 5%,2,IF(H5=0,1,"N/A"))))) And if C53, =IF(H5=34%,5,IF(H5=29%,4,IF(H5=22%,3,IF(H5=20. 5%,2,IF(H5=0,1,"N/A"))))) And if C52, =IF(H5=32%,5,IF(H5=27%,4,IF(H5=20%,3,IF(H5=18. 5%,2,IF(H5=0,1,"N/A"))))) And if C51, =IF(H5=30%,5,IF(H5=25%,4,IF(H5=18%,3,IF(H5=16. 5%,2,IF(H5=0,1,"N/A"))))) Thanks for your assistance. "David Biddulph" wrote: I assume that C5 can only be integer, and can't be less than 1? [If these constraints don't apply, then the formula can be tweaked, but you'll need to specify what you want for the unspecified conditions.] =IF(R5<265-5*MIN(C5,6),5,IF(R5<=275-5*MIN(C5,6),4,IF(R5<=285-5*MIN(C5,6),3,IF(R5<=310-5*MIN(C5,6),2,1 ) ) ) ) -- David Biddulph "PT40" wrote in message ... Hello, Me and a co-worker have been working on an issue we are trying to figure out. We have a basic formula but need it to apply different ratings based on a person's tenure. Tenure will be in Column C. I will put below what we would like to do, so if anyone has any easy suggestions, we are all ears. I say easy because we will need to replicate it about six times for different fields all based on tenure. Thank you Now if C5=6 then we would like: =IF(R5<235,5,IF(R5<=245,4,IF(R5<=255,3,IF(R5<=280, 2,IF(R5280,1) ) ) ) ) Or if C5=5 then we would like: =IF(R5<240,5,IF(R5<=250,4,IF(R5<=260,3,IF(R5<=285, 2,IF(R5285,1) ) ) ) ) or if C5=4 then we would like: =IF(R5<245,5,IF(R5<=255,4,IF(R5<=265,3,IF(R5<=290, 2,IF(R5290,1) ) ) ) ) or if C5=3 then we would like: =IF(R5<250,5,IF(R5<=260,4,IF(R5<=270,3,IF(R5<=295, 2,IF(R5295,1) ) ) ) ) or if C5=2 then we would like: =IF(R5<255,5,IF(R5<=265,4,IF(R5<=275,3,IF(R5<=300, 2,IF(R5300,1) ) ) ) ) or if C5=1 then we would like: =IF(R5<260,5,IF(R5<=270,4,IF(R5<=280,3,IF(R5<=305, 2,IF(R5305,1) ) ) ) ) Thanks again. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula based on conditions
The functions which I have used are IF and MIN. They are both described in
Excel help, with description of the syntax and examples of what they do, though it looks as if you already understand the IF function. The advice I give to anyone who doesn't understand a long formula is to break it down into manageable chunks and look at what each part does. For example, you can use =265-5*MIN(C5,6) and see how that responds to different values in C5 and see how the numbers compare with those in the corresponding place in your original formulae. It looks as if you could deal with your new requirements in exactly the same way. -- David Biddulph "PT40" wrote in message ... Rather, if you wouldn't mind explaining how that formula you gave me worked. I am at a loss, but it does seem to work. Thanks so much. "PT40" wrote: Yes, that would be correct. One would be the minimum for Column C. Let me see if this helps. One of the other conditions we have will only be based on four months. IF C5=4 then we would like =IF(H5=36%,5,IF(H5=31%,4,IF(H5=24%,3,IF(H5=22. 5%,2,IF(H5=0,1,"N/A"))))) And if C53, =IF(H5=34%,5,IF(H5=29%,4,IF(H5=22%,3,IF(H5=20. 5%,2,IF(H5=0,1,"N/A"))))) And if C52, =IF(H5=32%,5,IF(H5=27%,4,IF(H5=20%,3,IF(H5=18. 5%,2,IF(H5=0,1,"N/A"))))) And if C51, =IF(H5=30%,5,IF(H5=25%,4,IF(H5=18%,3,IF(H5=16. 5%,2,IF(H5=0,1,"N/A"))))) Thanks for your assistance. "David Biddulph" wrote: I assume that C5 can only be integer, and can't be less than 1? [If these constraints don't apply, then the formula can be tweaked, but you'll need to specify what you want for the unspecified conditions.] =IF(R5<265-5*MIN(C5,6),5,IF(R5<=275-5*MIN(C5,6),4,IF(R5<=285-5*MIN(C5,6),3,IF(R5<=310-5*MIN(C5,6),2,1 ) ) ) ) -- David Biddulph "PT40" wrote in message ... Hello, Me and a co-worker have been working on an issue we are trying to figure out. We have a basic formula but need it to apply different ratings based on a person's tenure. Tenure will be in Column C. I will put below what we would like to do, so if anyone has any easy suggestions, we are all ears. I say easy because we will need to replicate it about six times for different fields all based on tenure. Thank you Now if C5=6 then we would like: =IF(R5<235,5,IF(R5<=245,4,IF(R5<=255,3,IF(R5<=280, 2,IF(R5280,1) ) ) ) ) Or if C5=5 then we would like: =IF(R5<240,5,IF(R5<=250,4,IF(R5<=260,3,IF(R5<=285, 2,IF(R5285,1) ) ) ) ) or if C5=4 then we would like: =IF(R5<245,5,IF(R5<=255,4,IF(R5<=265,3,IF(R5<=290, 2,IF(R5290,1) ) ) ) ) or if C5=3 then we would like: =IF(R5<250,5,IF(R5<=260,4,IF(R5<=270,3,IF(R5<=295, 2,IF(R5295,1) ) ) ) ) or if C5=2 then we would like: =IF(R5<255,5,IF(R5<=265,4,IF(R5<=275,3,IF(R5<=300, 2,IF(R5300,1) ) ) ) ) or if C5=1 then we would like: =IF(R5<260,5,IF(R5<=270,4,IF(R5<=280,3,IF(R5<=305, 2,IF(R5305,1) ) ) ) ) Thanks again. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| shade cells based on conditions - i have more than 3 conditions | Excel Worksheet Functions | |||
| Counting based upon 2 conditions that are text based | Excel Discussion (Misc queries) | |||
| Formula to Extract value on 3 column based on two conditions | Excel Discussion (Misc queries) | |||
| Use different formula to calculation based on conditions | Excel Discussion (Misc queries) | |||
| Formula to return cell contents based on multiple conditions | Excel Worksheet Functions | |||
Linear Mode
