| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Getting around 'IF"
I want to calculate a letter grades based on marks. The letter grades
are divided up into a group of 10. F 0 to 49 D 50 to 54 C- 55 to 59 C 60 to 63 C+ 64 to 67 B- 68 to 71 B 72 to 75 B+ 76 to 79 A- 80 to 84 A 85 to 90 A+ 90 to 100 "IF" only allows 7 functions. How can I get around this restriciton? I'm somewhat of a novice when it comes to formula's so a bit of an example would be a great help. Thanks Rick |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Getting around 'IF"
have a look in the help index for LOOKUP
-- Don Guillett SalesAid Software "Dr Traffic" wrote in message ups.com... I want to calculate a letter grades based on marks. The letter grades are divided up into a group of 10. F 0 to 49 D 50 to 54 C- 55 to 59 C 60 to 63 C+ 64 to 67 B- 68 to 71 B 72 to 75 B+ 76 to 79 A- 80 to 84 A 85 to 90 A+ 90 to 100 "IF" only allows 7 functions. How can I get around this restriciton? I'm somewhat of a novice when it comes to formula's so a bit of an example would be a great help. Thanks Rick |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Getting around 'IF"
Create a lookup table, I called it Crit, with your symbols in the first
column, and your minimum score per symbol in the 2nd column. Where you want the result to show, leave a cell blank (I used E4), where you will enter the student's score. In the next cell enter =IF(E4="","",VLOOKUP(E4,Crit,2,TRUE)) (I used F4). If different, change E4 to the cell that will contain the student's score. -- Hth Kassie Kasselman "Dr Traffic" wrote: I want to calculate a letter grades based on marks. The letter grades are divided up into a group of 10. F 0 to 49 D 50 to 54 C- 55 to 59 C 60 to 63 C+ 64 to 67 B- 68 to 71 B 72 to 75 B+ 76 to 79 A- 80 to 84 A 85 to 90 A+ 90 to 100 "IF" only allows 7 functions. How can I get around this restriciton? I'm somewhat of a novice when it comes to formula's so a bit of an example would be a great help. Thanks Rick |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Getting around 'IF"
You could use a VLOOKUP table with two columns.
Column A with the scores and Column B with the grades. Or this alternative.................. With column of scores in A enter this in B1 and double-click to copy down. Column B will return the letter grades. =LOOKUP(A1,{0,50,55,60,64,68,72,76,80,85,91,101},{ "F","D","C-","C","C+","B-","B","B+","A-","A","A+"}) Gord Dibben MS Excel MVP On 31 Dec 2006 08:33:25 -0800, "Dr Traffic" wrote: I want to calculate a letter grades based on marks. The letter grades are divided up into a group of 10. F 0 to 49 D 50 to 54 C- 55 to 59 C 60 to 63 C+ 64 to 67 B- 68 to 71 B 72 to 75 B+ 76 to 79 A- 80 to 84 A 85 to 90 A+ 90 to 100 "IF" only allows 7 functions. How can I get around this restriciton? I'm somewhat of a novice when it comes to formula's so a bit of an example would be a great help. Thanks Rick |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Getting around 'IF"
You can have a datalist in an "out-of-the-way" location on your WS, or you
can incorporate it within the formula itself. Say the number grades were in Column D, from D1 down. Enter this in E1: =IF(D1,LOOKUP(D1,{0,50,55,60,64,68,72,76,80,85,90; "F","D","C-","C","C+","B-" ,"B","B+","A-","A","A+"}),"") And copy down as needed. To use a datalist, enter this in say Y1 to Z11: Y Z 0 F 50 D 55 C- 60 C 64 C+ 68 B- 72 B 76 B+ 80 A- 85 A 90 A+ And then enter this formula in E1, and copy down: =IF(D1,LOOKUP(D1,Y1:Z11),"") -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Dr Traffic" wrote in message ups.com... I want to calculate a letter grades based on marks. The letter grades are divided up into a group of 10. F 0 to 49 D 50 to 54 C- 55 to 59 C 60 to 63 C+ 64 to 67 B- 68 to 71 B 72 to 75 B+ 76 to 79 A- 80 to 84 A 85 to 90 A+ 90 to 100 "IF" only allows 7 functions. How can I get around this restriciton? I'm somewhat of a novice when it comes to formula's so a bit of an example would be a great help. Thanks Rick |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Getting around 'IF"
Forgot to include absolute references in the last formula to enable copying:
=IF(D1,LOOKUP(D1,$Y$1:$Z$11),"") -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Ragdyer" wrote in message ... You can have a datalist in an "out-of-the-way" location on your WS, or you can incorporate it within the formula itself. Say the number grades were in Column D, from D1 down. Enter this in E1: =IF(D1,LOOKUP(D1,{0,50,55,60,64,68,72,76,80,85,90; "F","D","C-","C","C+","B-" ,"B","B+","A-","A","A+"}),"") And copy down as needed. To use a datalist, enter this in say Y1 to Z11: Y Z 0 F 50 D 55 C- 60 C 64 C+ 68 B- 72 B 76 B+ 80 A- 85 A 90 A+ And then enter this formula in E1, and copy down: =IF(D1,LOOKUP(D1,Y1:Z11),"") -- HTH, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "Dr Traffic" wrote in message ups.com... I want to calculate a letter grades based on marks. The letter grades are divided up into a group of 10. F 0 to 49 D 50 to 54 C- 55 to 59 C 60 to 63 C+ 64 to 67 B- 68 to 71 B 72 to 75 B+ 76 to 79 A- 80 to 84 A 85 to 90 A+ 90 to 100 "IF" only allows 7 functions. How can I get around this restriciton? I'm somewhat of a novice when it comes to formula's so a bit of an example would be a great help. Thanks Rick |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Getting around 'IF"
Hi Ragdyer,
Personally I like VLOOKUP however it can be done with IF by expanding the idea shown below: =IF(H1<50,"F","")&IF(AND(H149,H1<55),"D","") You will also be able to do it in 2007 with nested IF's since you will be allowed 50 levels deep of nesting if my memory servers me. -- Cheers, Shane Devenshire "Ragdyer" wrote: Forgot to include absolute references in the last formula to enable copying: =IF(D1,LOOKUP(D1,$Y$1:$Z$11),"") -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Ragdyer" wrote in message ... You can have a datalist in an "out-of-the-way" location on your WS, or you can incorporate it within the formula itself. Say the number grades were in Column D, from D1 down. Enter this in E1: =IF(D1,LOOKUP(D1,{0,50,55,60,64,68,72,76,80,85,90; "F","D","C-","C","C+","B-" ,"B","B+","A-","A","A+"}),"") And copy down as needed. To use a datalist, enter this in say Y1 to Z11: Y Z 0 F 50 D 55 C- 60 C 64 C+ 68 B- 72 B 76 B+ 80 A- 85 A 90 A+ And then enter this formula in E1, and copy down: =IF(D1,LOOKUP(D1,Y1:Z11),"") -- HTH, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "Dr Traffic" wrote in message ups.com... I want to calculate a letter grades based on marks. The letter grades are divided up into a group of 10. F 0 to 49 D 50 to 54 C- 55 to 59 C 60 to 63 C+ 64 to 67 B- 68 to 71 B 72 to 75 B+ 76 to 79 A- 80 to 84 A 85 to 90 A+ 90 to 100 "IF" only allows 7 functions. How can I get around this restriciton? I'm somewhat of a novice when it comes to formula's so a bit of an example would be a great help. Thanks Rick |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Getting around 'IF"
|
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Getting around 'IF"
Are you saying that you would (prefer to) *USE* 50 levels of an If()
function? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "ShaneDevenshire" wrote in message ... Hi Ragdyer, Personally I like VLOOKUP however it can be done with IF by expanding the idea shown below: =IF(H1<50,"F","")&IF(AND(H149,H1<55),"D","") You will also be able to do it in 2007 with nested IF's since you will be allowed 50 levels deep of nesting if my memory servers me. -- Cheers, Shane Devenshire "Ragdyer" wrote: Forgot to include absolute references in the last formula to enable copying: =IF(D1,LOOKUP(D1,$Y$1:$Z$11),"") -- Regards, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "Ragdyer" wrote in message ... You can have a datalist in an "out-of-the-way" location on your WS, or you can incorporate it within the formula itself. Say the number grades were in Column D, from D1 down. Enter this in E1: =IF(D1,LOOKUP(D1,{0,50,55,60,64,68,72,76,80,85,90; "F","D","C-","C","C+","B-" ,"B","B+","A-","A","A+"}),"") And copy down as needed. To use a datalist, enter this in say Y1 to Z11: Y Z 0 F 50 D 55 C- 60 C 64 C+ 68 B- 72 B 76 B+ 80 A- 85 A 90 A+ And then enter this formula in E1, and copy down: =IF(D1,LOOKUP(D1,Y1:Z11),"") -- HTH, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "Dr Traffic" wrote in message ups.com... I want to calculate a letter grades based on marks. The letter grades are divided up into a group of 10. F 0 to 49 D 50 to 54 C- 55 to 59 C 60 to 63 C+ 64 to 67 B- 68 to 71 B 72 to 75 B+ 76 to 79 A- 80 to 84 A 85 to 90 A+ 90 to 100 "IF" only allows 7 functions. How can I get around this restriciton? I'm somewhat of a novice when it comes to formula's so a bit of an example would be a great help. Thanks Rick |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Getting around 'IF"
Hi,
If you read my original message you will see that I prefer VLOOKUP, but I just want to inform users of features in the upcoming version. Personally, when I see seven levels of nesting I almost always assume there could have been a better way. But Excel users have been complaining for years about the 7 level limit since DOS versions of Lotus 1-2-3 and Quartro Pro could do more. Happy new year to all! -- Cheers, Shane Devenshire "Ragdyer" wrote: Are you saying that you would (prefer to) *USE* 50 levels of an If() function? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "ShaneDevenshire" wrote in message ... Hi Ragdyer, Personally I like VLOOKUP however it can be done with IF by expanding the idea shown below: =IF(H1<50,"F","")&IF(AND(H149,H1<55),"D","") You will also be able to do it in 2007 with nested IF's since you will be allowed 50 levels deep of nesting if my memory servers me. -- Cheers, Shane Devenshire "Ragdyer" wrote: Forgot to include absolute references in the last formula to enable copying: =IF(D1,LOOKUP(D1,$Y$1:$Z$11),"") -- Regards, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "Ragdyer" wrote in message ... You can have a datalist in an "out-of-the-way" location on your WS, or you can incorporate it within the formula itself. Say the number grades were in Column D, from D1 down. Enter this in E1: =IF(D1,LOOKUP(D1,{0,50,55,60,64,68,72,76,80,85,90; "F","D","C-","C","C+","B-" ,"B","B+","A-","A","A+"}),"") And copy down as needed. To use a datalist, enter this in say Y1 to Z11: Y Z 0 F 50 D 55 C- 60 C 64 C+ 68 B- 72 B 76 B+ 80 A- 85 A 90 A+ And then enter this formula in E1, and copy down: =IF(D1,LOOKUP(D1,Y1:Z11),"") -- HTH, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "Dr Traffic" wrote in message ups.com... I want to calculate a letter grades based on marks. The letter grades are divided up into a group of 10. F 0 to 49 D 50 to 54 C- 55 to 59 C 60 to 63 C+ 64 to 67 B- 68 to 71 B 72 to 75 B+ 76 to 79 A- 80 to 84 A 85 to 90 A+ 90 to 100 "IF" only allows 7 functions. How can I get around this restriciton? I'm somewhat of a novice when it comes to formula's so a bit of an example would be a great help. Thanks Rick |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to simplify "multiple if" formula | Excel Worksheet Functions | |||
| "Count If" 3 criterias are fulfilled | Excel Discussion (Misc queries) | |||
| Using 'if" To Return An Entire Row? | Excel Discussion (Misc queries) | |||
Hybrid Mode
