Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Meaning In English
I'm trying to develop a point system here at work to give a certain amount of points for receiving a certain grade. I'm not that well versed in Excel so I don't know what these formulas mean in English. =IF(C6="","",IF(C6<$C$12,0,IF(C6$C$14,$D$14,IF(C6 <=$C$13,((C6-$C$12)/($C$13-$C$12))*($D$13-$D$12)+$D$12,IF(C6$C$13,((C6-$C$13)/($C$14-$C$13))*($D$14-$D$13)+$D$13,0))))) Target Points C12 594.49 1.25 D12 C13 540.44 5.00 D13 C14 491.31 10.00 D14 With the formula above, when I enter 594.49 into cell C6 I'm getting 10.00 pts when I should be getting 1.25 points. I created this one and it works exactly as I intended, but I can't just copy and paste. I will need to create about 10 of these little point matrix's and all of them have different targets and different reward points. =IF(G6="","",IF(G6<$G$12,0,IF(G6$G$14,$H$14,IF(G6 <=$G$13,((G6-$G$12)/($G$13-$G$12))*($H$13-$H$12)+$H$12,IF(G6$G$13,((G6-$G$13)/($G$14-$G$13))*($H$14-$H$13)+$H$13,0))))) Target Points G12 4.50 5.00 H12 G13 4.75 20.00 H13 G14 5.00 40.00 H14 Any help would be greatly appreciated. -Chuck- -- Dropdown3 ------------------------------------------------------------------------ Dropdown3's Profile: http://www.excelforum.com/member.php...o&userid=30321 View this thread: http://www.excelforum.com/showthread...hreadid=505440 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Meaning In English
You could use a VLOOKUP function to locate an Excact match instead of a
multi-conditional IF statement. The VLOOKUP formula has the following syntax: VLOOKUP(ValueToLookUp,LookUpTableRange,ReturnValue Column,FALSE) The FALSE statement at the end of the lookup indicates an exact match only. As an example, if your value are in Column A row 1 and the macth in in Column B Row1, and the lookup table that holds the results is on Sheet2, cells A1 through B3, the formula would be the vlookup below: A B 4.50 VLOOKUP(A1,Sheet2!$A$1:$B$3,2,FALSE) 4.75 5 The lookup Table would look like this: A B 4.50 5.0 4.75 20.00 5 40.00 -- Kevin Backmann "Dropdown3" wrote: I'm trying to develop a point system here at work to give a certain amount of points for receiving a certain grade. I'm not that well versed in Excel so I don't know what these formulas mean in English. =IF(C6="","",IF(C6<$C$12,0,IF(C6$C$14,$D$14,IF(C6 <=$C$13,((C6-$C$12)/($C$13-$C$12))*($D$13-$D$12)+$D$12,IF(C6$C$13,((C6-$C$13)/($C$14-$C$13))*($D$14-$D$13)+$D$13,0))))) Target Points C12 594.49 1.25 D12 C13 540.44 5.00 D13 C14 491.31 10.00 D14 With the formula above, when I enter 594.49 into cell C6 I'm getting 10.00 pts when I should be getting 1.25 points. I created this one and it works exactly as I intended, but I can't just copy and paste. I will need to create about 10 of these little point matrix's and all of them have different targets and different reward points. =IF(G6="","",IF(G6<$G$12,0,IF(G6$G$14,$H$14,IF(G6 <=$G$13,((G6-$G$12)/($G$13-$G$12))*($H$13-$H$12)+$H$12,IF(G6$G$13,((G6-$G$13)/($G$14-$G$13))*($H$14-$H$13)+$H$13,0))))) Target Points G12 4.50 5.00 H12 G13 4.75 20.00 H13 G14 5.00 40.00 H14 Any help would be greatly appreciated. -Chuck- -- Dropdown3 ------------------------------------------------------------------------ Dropdown3's Profile: http://www.excelforum.com/member.php...o&userid=30321 View this thread: http://www.excelforum.com/showthread...hreadid=505440 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Meaning In English
The numbers will vary and will hardly if ever hit exactly on the target. How does this affect the VLOOKUP and the number of reward points assigned? Lastly, I want to stick with the IF statement because I'm continuing what someone else has started and I want to keep the format the same. -Chuck- -- Dropdown3 ------------------------------------------------------------------------ Dropdown3's Profile: http://www.excelforum.com/member.php...o&userid=30321 View this thread: http://www.excelforum.com/showthread...hreadid=505440 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Meaning In English
If you drop the FALSE statement out of the VLOOKUP, which is optional, it
finds the closest match, not an exact match. -- Kevin Backmann "Dropdown3" wrote: The numbers will vary and will hardly if ever hit exactly on the target. How does this affect the VLOOKUP and the number of reward points assigned? Lastly, I want to stick with the IF statement because I'm continuing what someone else has started and I want to keep the format the same. -Chuck- -- Dropdown3 ------------------------------------------------------------------------ Dropdown3's Profile: http://www.excelforum.com/member.php...o&userid=30321 View this thread: http://www.excelforum.com/showthread...hreadid=505440 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Meaning In English
Maybe someone will take a look at my first post and steer me in the right direction with my IF statement. Kevin, sometimes an employees data will hit the target, other times they won't. If it doesn't hit the target, I need Excel to be able to calculate just how many points they should receive. If a score of 10.00 yields 3.00 reward points, what if they score 7.75 points, or 9.00 points. How many reward points would they then receive. Hey man, I really appreciate your posts, I'm about to pull my hair out ;) -Chuck- -- Dropdown3 ------------------------------------------------------------------------ Dropdown3's Profile: http://www.excelforum.com/member.php...o&userid=30321 View this thread: http://www.excelforum.com/showthread...hreadid=505440 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Meaning In English
=IF(C6="","",IF(C6<$C$12,0,IF(C6$C$14,$D$14,IF(C6 <=$C$13,((C6-$C$12)/($C$13-$C$12))*($D$13-$D$12)+$D$12,IF(C6$C$13,((C6-$C$13)/($C$14-$C$13))*($D$14-$D$13)+$D$13,0))))) The important point as far as your example is, If C6 is greater than C14 then Return the value that is in D14. C14 491.31 10.00 D14 With the formula above, when I enter 594.49 into cell C6 I'm getting 10.00 pts when I should be getting 1.25 points C6 is 594.49 which is greater than 491.31 therefore it is returning 10. -- Kevin Vaughn "Dropdown3" wrote: I'm trying to develop a point system here at work to give a certain amount of points for receiving a certain grade. I'm not that well versed in Excel so I don't know what these formulas mean in English. =IF(C6="","",IF(C6<$C$12,0,IF(C6$C$14,$D$14,IF(C6 <=$C$13,((C6-$C$12)/($C$13-$C$12))*($D$13-$D$12)+$D$12,IF(C6$C$13,((C6-$C$13)/($C$14-$C$13))*($D$14-$D$13)+$D$13,0))))) Target Points C12 594.49 1.25 D12 C13 540.44 5.00 D13 C14 491.31 10.00 D14 With the formula above, when I enter 594.49 into cell C6 I'm getting 10.00 pts when I should be getting 1.25 points. I created this one and it works exactly as I intended, but I can't just copy and paste. I will need to create about 10 of these little point matrix's and all of them have different targets and different reward points. =IF(G6="","",IF(G6<$G$12,0,IF(G6$G$14,$H$14,IF(G6 <=$G$13,((G6-$G$12)/($G$13-$G$12))*($H$13-$H$12)+$H$12,IF(G6$G$13,((G6-$G$13)/($G$14-$G$13))*($H$14-$H$13)+$H$13,0))))) Target Points G12 4.50 5.00 H12 G13 4.75 20.00 H13 G14 5.00 40.00 H14 Any help would be greatly appreciated. -Chuck- -- Dropdown3 ------------------------------------------------------------------------ Dropdown3's Profile: http://www.excelforum.com/member.php...o&userid=30321 View this thread: http://www.excelforum.com/showthread...hreadid=505440 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Meaning In English
Here is my current, incorrect formula: =IF(Q5="","",IF(Q5$Q$25,0,IF(Q5=$Q$26,((Q5-$Q$25)/($Q$26-$Q$25))*($R$26-$R$25)+$R$25,IF(Q5<$Q$26,((Q5-$Q$26)/($Q$27-$Q$26))*($R$27-$R$26)+$R$26,0)))) Target................Points Min......Q24 3.40 1.25 R24 Mid......Q25 3.06 5.00 R25 Max.....Q26 2.45 10.00 R26 As you can see, the lower the score the better. The most points you SHOULD get would be 10.00 points. However, if I get a score of 2.44, which is really good, I get 10.08 instead of 10.00. I want the cut-off to be 10.00. Should be If greater than or equal to R26 then receive 10.00 points. Where am I going wrong? -Chuck- -- Dropdown3 ------------------------------------------------------------------------ Dropdown3's Profile: http://www.excelforum.com/member.php...o&userid=30321 View this thread: http://www.excelforum.com/showthread...hreadid=505440 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Meaning In English
It is not clear to me what you are trying to accomplish, but this will limit
the answer to 10: =IF(Q5="","",IF(Q5<$Q$26,10,IF(Q5=$Q$26,((Q5-$Q$25)/($Q$26-$Q$25))*($R$26-$R$25)+$R$25,IF(Q5<$Q$26,((Q5-$Q$26)/($Q$27-$Q$26))*($R$27-$R$26)+$R$26,0)))) Here is an example IF construction: =IF(Q5<Q26,"Less than q26",IF(Q5<Q25,"less than q25",IF(Q5<Q24,"less than q24",0))) -- Damon Longworth 2006 East Coast Excel User Conference April 19/21st, 2006 Holiday Inn, Boardwalk Atlantic City, New Jersey Early Bird Registration Now Open!! www.ExcelUserConference.com 2006 UK Excel User Conference Summer, 2006 London, England "Dropdown3" wrote in message ... Here is my current, incorrect formula: =IF(Q5="","",IF(Q5$Q$25,0,IF(Q5=$Q$26,((Q5-$Q$25)/($Q$26-$Q$25))*($R$26-$R$25)+$R$25,IF(Q5<$Q$26,((Q5-$Q$26)/($Q$27-$Q$26))*($R$27-$R$26)+$R$26,0)))) Target................Points Min......Q24 3.40 1.25 R24 Mid......Q25 3.06 5.00 R25 Max.....Q26 2.45 10.00 R26 As you can see, the lower the score the better. The most points you SHOULD get would be 10.00 points. However, if I get a score of 2.44, which is really good, I get 10.08 instead of 10.00. I want the cut-off to be 10.00. Should be If greater than or equal to R26 then receive 10.00 points. Where am I going wrong? -Chuck- -- Dropdown3 ------------------------------------------------------------------------ Dropdown3's Profile: http://www.excelforum.com/member.php...o&userid=30321 View this thread: http://www.excelforum.com/showthread...hreadid=505440 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Meaning In English
Damon:Your formula is the closest so far. However, it returns points if the Target is 3.41 or higher, which it should return 0.00 points Target.......................Points Min......Q24...3.40.....1.25--- R24 Mid......Q25...3.06......5.00--- R25 Max.....Q26...2.45......10.00-- R26 Here is what I want from my point system: If Q5 less than Q24 but greater than 3.06 you should get R24 value. If Q5 is greater than Q24 you won't receive any points. If Q5 is greater than Q25 but less than R25 you should get R25 value. If Q5 is less than or equal to Q26 then R26. If the employee falls in the score range of 3.40 or less, they should receive a certain amount of points. If they ever score more than 3.40, they will not receive any points. The amount of points cannot be set to 10.00, 5.00, or 1.25 when a certain score is entered into the cell Q5. I want the point output to adjust to the number in cell Q5, but an employee cannot earn more than 10.00 points regardless of how low (good) a score because R26 is the most amount of points they can earn. -Chuck- -- Dropdown3 ------------------------------------------------------------------------ Dropdown3's Profile: http://www.excelforum.com/member.php...o&userid=30321 View this thread: http://www.excelforum.com/showthread...hreadid=505440 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Meaning In English
I GOT IT!! =IF(Q5="","",IF(Q5$Q$25,0,IF(Q5<$Q$27,$R$27,IF(Q5 =$Q$26,((Q5-$Q$25)/($Q$26-$Q$25))*($R$26-$R$25)+$R$25,IF(Q5<$Q$26,((Q5-$Q$26)/($Q$27-$Q$26))*($R$27-$R$26)+$R$26,0))))) Even though none of the replies hit exactly what I needed, they all helped in getting me to my answer. Thanks. -Chuck- -- Dropdown3 ------------------------------------------------------------------------ Dropdown3's Profile: http://www.excelforum.com/member.php...o&userid=30321 View this thread: http://www.excelforum.com/showthread...hreadid=505440 |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Meaning In English
Try something similar to:
=IF(Q5<=Q26,R26,IF(Q5<=Q25,R25,IF(Q5<=Q24,R24,0))) Adjust the TRUE portion to your desired calculation. It seems to work here from my understanding of your requirements. -- Damon Longworth 2006 East Coast Excel User Conference April 19/21st, 2006 Holiday Inn, Boardwalk Atlantic City, New Jersey Early Bird Registration Now Open!! www.ExcelUserConference.com 2006 UK Excel User Conference Summer, 2006 London, England "Dropdown3" wrote in message ... Damon:Your formula is the closest so far. However, it returns points if the Target is 3.41 or higher, which it should return 0.00 points Target.......................Points Min......Q24...3.40.....1.25--- R24 Mid......Q25...3.06......5.00--- R25 Max.....Q26...2.45......10.00-- R26 Here is what I want from my point system: If Q5 less than Q24 but greater than 3.06 you should get R24 value. If Q5 is greater than Q24 you won't receive any points. If Q5 is greater than Q25 but less than R25 you should get R25 value. If Q5 is less than or equal to Q26 then R26. If the employee falls in the score range of 3.40 or less, they should receive a certain amount of points. If they ever score more than 3.40, they will not receive any points. The amount of points cannot be set to 10.00, 5.00, or 1.25 when a certain score is entered into the cell Q5. I want the point output to adjust to the number in cell Q5, but an employee cannot earn more than 10.00 points regardless of how low (good) a score because R26 is the most amount of points they can earn. -Chuck- -- Dropdown3 ------------------------------------------------------------------------ Dropdown3's Profile: http://www.excelforum.com/member.php...o&userid=30321 View this thread: http://www.excelforum.com/showthread...hreadid=505440 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula Meaning | Excel Discussion (Misc queries) | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) |