| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula to another forumula?
We would like to have a formula that will calculate differently based on
Tenure of our staff. Column B will hold the Tenure. So say cell B5 equals 6 or greater, then =IF(C5=17.5%,5,IF(C5=16.25%,4,IF(C5=14.25%,3,IF (C5=13%,2,IF(C5=0,1,"N/A"))))) and if B5 equals 5 then =IF(C5=16.5%,5,IF(C5=15.25%,4,IF(C5=13.25%,3,IF (C5=12%,2,IF(C5=0,1,"N/A"))))) and so on down to B5 equaling down to 1 or less. How do I do this? Forgive me for being kind of a hack. Max had tried to help earlier but had a part of the formula I didn't understand. Thank you |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula to another forumula?
=IF(C5=11.5%+1%*MEDIAN(1,B5,6),5,IF(C5=10.25%+1% *MEDIAN(1,B5,6),4,IF(C5=8.25%+1%*MEDIAN(1,B5,6),3 ,IF(C5=7%+1%*MEDIAN(1,B5,6),2,IF(C5=0,1,"N/A")))))
-- David Biddulph "57Caddy" wrote in message ... We would like to have a formula that will calculate differently based on Tenure of our staff. Column B will hold the Tenure. So say cell B5 equals 6 or greater, then =IF(C5=17.5%,5,IF(C5=16.25%,4,IF(C5=14.25%,3,IF (C5=13%,2,IF(C5=0,1,"N/A"))))) and if B5 equals 5 then =IF(C5=16.5%,5,IF(C5=15.25%,4,IF(C5=13.25%,3,IF (C5=12%,2,IF(C5=0,1,"N/A"))))) and so on down to B5 equaling down to 1 or less. How do I do this? Forgive me for being kind of a hack. Max had tried to help earlier but had a part of the formula I didn't understand. Thank you |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula to another forumula?
David, correct me if I am wrong, but that appears to be adding the fields
together. What I would like to do is have one set of ratings for someone with one month or less, another for someone at 2 months, until we get to 6 months where everyone more than 6 months is rated the same. "David Biddulph" wrote: =IF(C5=11.5%+1%*MEDIAN(1,B5,6),5,IF(C5=10.25%+1% *MEDIAN(1,B5,6),4,IF(C5=8.25%+1%*MEDIAN(1,B5,6),3 ,IF(C5=7%+1%*MEDIAN(1,B5,6),2,IF(C5=0,1,"N/A"))))) -- David Biddulph "57Caddy" wrote in message ... We would like to have a formula that will calculate differently based on Tenure of our staff. Column B will hold the Tenure. So say cell B5 equals 6 or greater, then =IF(C5=17.5%,5,IF(C5=16.25%,4,IF(C5=14.25%,3,IF (C5=13%,2,IF(C5=0,1,"N/A"))))) and if B5 equals 5 then =IF(C5=16.5%,5,IF(C5=15.25%,4,IF(C5=13.25%,3,IF (C5=12%,2,IF(C5=0,1,"N/A"))))) and so on down to B5 equaling down to 1 or less. How do I do this? Forgive me for being kind of a hack. Max had tried to help earlier but had a part of the formula I didn't understand. Thank you |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula to another forumula?
Adding which fields together?
Have you tried my formula and compared it with what you wanted? If so, and if you get a different result from what you expected, then please explain what numbers you had as your inputs, what you got as the result from my formula, and what you expected from your formula? If you don't understand my formula, break it down to manageable chunks and look at what each part does and compare it with what you asked for. -- David Biddulph "57Caddy" wrote in message ... David, correct me if I am wrong, but that appears to be adding the fields together. What I would like to do is have one set of ratings for someone with one month or less, another for someone at 2 months, until we get to 6 months where everyone more than 6 months is rated the same. "David Biddulph" wrote: =IF(C5=11.5%+1%*MEDIAN(1,B5,6),5,IF(C5=10.25%+1% *MEDIAN(1,B5,6),4,IF(C5=8.25%+1%*MEDIAN(1,B5,6),3 ,IF(C5=7%+1%*MEDIAN(1,B5,6),2,IF(C5=0,1,"N/A"))))) -- David Biddulph "57Caddy" wrote in message ... We would like to have a formula that will calculate differently based on Tenure of our staff. Column B will hold the Tenure. So say cell B5 equals 6 or greater, then =IF(C5=17.5%,5,IF(C5=16.25%,4,IF(C5=14.25%,3,IF (C5=13%,2,IF(C5=0,1,"N/A"))))) and if B5 equals 5 then =IF(C5=16.5%,5,IF(C5=15.25%,4,IF(C5=13.25%,3,IF (C5=12%,2,IF(C5=0,1,"N/A"))))) and so on down to B5 equaling down to 1 or less. How do I do this? Forgive me for being kind of a hack. Max had tried to help earlier but had a part of the formula I didn't understand. Thank you |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula to another forumula?
David,
Using that formula I have a banker with a 8.25% with 2 months tenure being rated a 3 when they should be a one. It is totally my fault. Let me give you exact figures. Month One: 11.5% or higher = 5, 10.25%-11.49% = 4, 8.25%-10.24% = 3, 7.0%-8.24%=2, 6.99% = 1 Month Two: 13.5% or higher = 5, 12.25%-13.49% = 4, 10.25%-12.24% = 3, 9.0%-10.24%=2, 8.99% = 1 Month Three: 15.5% or higher = 5, 14.25%-15.49% = 4, 12.25%-14.24% = 3, 11%-12.24% = 2, 10.99% = 1. Month four and above for this one: 17.5% or higher = 5, 16.25%-17.49% = 4, 14.25%-16.24% = 3, 13%-14.24% = 2, 12.99%=1 I appreciate your assistance. "David Biddulph" wrote: Adding which fields together? Have you tried my formula and compared it with what you wanted? If so, and if you get a different result from what you expected, then please explain what numbers you had as your inputs, what you got as the result from my formula, and what you expected from your formula? If you don't understand my formula, break it down to manageable chunks and look at what each part does and compare it with what you asked for. -- David Biddulph "57Caddy" wrote in message ... David, correct me if I am wrong, but that appears to be adding the fields together. What I would like to do is have one set of ratings for someone with one month or less, another for someone at 2 months, until we get to 6 months where everyone more than 6 months is rated the same. "David Biddulph" wrote: =IF(C5=11.5%+1%*MEDIAN(1,B5,6),5,IF(C5=10.25%+1% *MEDIAN(1,B5,6),4,IF(C5=8.25%+1%*MEDIAN(1,B5,6),3 ,IF(C5=7%+1%*MEDIAN(1,B5,6),2,IF(C5=0,1,"N/A"))))) -- David Biddulph "57Caddy" wrote in message ... We would like to have a formula that will calculate differently based on Tenure of our staff. Column B will hold the Tenure. So say cell B5 equals 6 or greater, then =IF(C5=17.5%,5,IF(C5=16.25%,4,IF(C5=14.25%,3,IF (C5=13%,2,IF(C5=0,1,"N/A"))))) and if B5 equals 5 then =IF(C5=16.5%,5,IF(C5=15.25%,4,IF(C5=13.25%,3,IF (C5=12%,2,IF(C5=0,1,"N/A"))))) and so on down to B5 equaling down to 1 or less. How do I do this? Forgive me for being kind of a hack. Max had tried to help earlier but had a part of the formula I didn't understand. Thank you |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula to another forumula?
My formula gives a result of 1 for your input figures of 8.25% and 2. If
you've got a result of 3, then you've not got the formula right. Did you copy and paste, or did you try to retype? Are you sure that you didn't have a problem with the line break in the newsgroup message? If you want to copy your formula back here, then I can look to see what you got wrong if you can't spot it yourself. I haven't changed the formula to tie in with your latest figures, as I assume that you don't really have gaps (with undefined output) between 17.49% and 17.5%, or between 16.24% and 16.25%, or between 14.24% and 14.25%, and so on. I assume that your original specification was correct where you'd defined the conditions as =17.5%, =16.25%, and so on? -- David Biddulph "57Caddy" wrote in message ... David, Using that formula I have a banker with a 8.25% with 2 months tenure being rated a 3 when they should be a one. It is totally my fault. Let me give you exact figures. Month One: 11.5% or higher = 5, 10.25%-11.49% = 4, 8.25%-10.24% = 3, 7.0%-8.24%=2, 6.99% = 1 Month Two: 13.5% or higher = 5, 12.25%-13.49% = 4, 10.25%-12.24% = 3, 9.0%-10.24%=2, 8.99% = 1 Month Three: 15.5% or higher = 5, 14.25%-15.49% = 4, 12.25%-14.24% = 3, 11%-12.24% = 2, 10.99% = 1. Month four and above for this one: 17.5% or higher = 5, 16.25%-17.49% = 4, 14.25%-16.24% = 3, 13%-14.24% = 2, 12.99%=1 I appreciate your assistance. "David Biddulph" wrote: Adding which fields together? Have you tried my formula and compared it with what you wanted? If so, and if you get a different result from what you expected, then please explain what numbers you had as your inputs, what you got as the result from my formula, and what you expected from your formula? If you don't understand my formula, break it down to manageable chunks and look at what each part does and compare it with what you asked for. -- David Biddulph "57Caddy" wrote in message ... David, correct me if I am wrong, but that appears to be adding the fields together. What I would like to do is have one set of ratings for someone with one month or less, another for someone at 2 months, until we get to 6 months where everyone more than 6 months is rated the same. "David Biddulph" wrote: =IF(C5=11.5%+1%*MEDIAN(1,B5,6),5,IF(C5=10.25%+1% *MEDIAN(1,B5,6),4,IF(C5=8.25%+1%*MEDIAN(1,B5,6),3 ,IF(C5=7%+1%*MEDIAN(1,B5,6),2,IF(C5=0,1,"N/A"))))) -- David Biddulph "57Caddy" wrote in message ... We would like to have a formula that will calculate differently based on Tenure of our staff. Column B will hold the Tenure. So say cell B5 equals 6 or greater, then =IF(C5=17.5%,5,IF(C5=16.25%,4,IF(C5=14.25%,3,IF (C5=13%,2,IF(C5=0,1,"N/A"))))) and if B5 equals 5 then =IF(C5=16.5%,5,IF(C5=15.25%,4,IF(C5=13.25%,3,IF (C5=12%,2,IF(C5=0,1,"N/A"))))) and so on down to B5 equaling down to 1 or less. How do I do this? Forgive me for being kind of a hack. Max had tried to help earlier but had a part of the formula I didn't understand. Thank you |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Need help with a look up forumula I haven't seen before | Excel Worksheet Functions | |||
| I Need a forumula or VBA code | Excel Discussion (Misc queries) | |||
| If more than condition forumula | Excel Worksheet Functions | |||
| Forumula help using Countif | Excel Worksheet Functions | |||
| Help with FORUMULA | Excel Discussion (Misc queries) | |||
Hybrid Mode
