Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can Text be Treated as Values for use in Calculations with Numbers?
Hi All,
I would like for teachers to be able to enter point values (0, 20, 78, 99, etc.) AND letter grades: A+, B-, C, as well as selected other letters-I for incomplete, N for no credit, and X for excused-in the same cells and have Excel use both the point values and letter grades in formulas. If teachers enter an A- or a C+ into a cell, is there a way for Excel to treat that like the average or midpoint of the A- or C+ range? The A- range is from 90 to 93.33, and the C+ range is 76.66 to 79.99. So, an A- would equal 91.67, and a C+ would = 78.33. =============================== Here's more info on what I have right now... There are columns in which numeric point values only are entered for assignment scores. For instance: Student X AA13 = 20 Student Y AA14 = 17 Student Z AA15 = <blank because the student had an excused absence In cell AA10, the max point value of 20 was entered for that assignment. Assignments were added to the right of col. AA almost daily. Note that if a teacher used letter grades instead of point values, the max values in row 10 would not be needed<< Scores were averaged across a quarter. So, the following formulas calculated the points possible, average score, and a % based off of assignment points: For Student X... P13 = SUMIF(AA13:AX13,"0",AA$10:AX$10) Q13 =SUM(AA13:AX13) R13 =AVERAGE(IF(($AA13:AX13<"")*($AA$10:AX$10<0),$AA 13:AX13/$AA $10:AX$10))*100 In determining final letter grades, I did some weighting with some other scores, but ultimately used this formula in col. X: =IF(V13<"",HLOOKUP(V13/100,Hgrades,2),"") So, there were 2 cols. in a sheet named Fields that the HLOOKUP referenced: 0 N 40 I 50 F 60 D- 63.33 D 66.67 D+ 70 C- 73.33 C 76.67 C+ (In the above, a C+ would = 78.33) 80 B- (In the above, a B- would = 71.67) 83.33 B (In the above, a B would = 85.00) 86.67 B+ (In the above, a B+ would = 88.33) 90 A- (In the above, an A- would equal 91.67) 93.33 A 96.67 A+ =============================== Very curious...Thanks a lot! Arnold |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can Text be Treated as Values for use in Calculations with Numbers
Why not just put the computed averages to the right of the 2nd column in your
table. then you could lookup the Letter grade with Hlookup and return the average. =if(V3<"",if(isnumber(V13),HLOOKUP(V13/100,Hgrades,2),""),HLOOKUP(130,Offset(Hgrades,0,1) ,2),""),"") -- Regards, Tom Ogilvy "Arnold" wrote: Hi All, I would like for teachers to be able to enter point values (0, 20, 78, 99, etc.) AND letter grades: A+, B-, C, as well as selected other letters-I for incomplete, N for no credit, and X for excused-in the same cells and have Excel use both the point values and letter grades in formulas. If teachers enter an A- or a C+ into a cell, is there a way for Excel to treat that like the average or midpoint of the A- or C+ range? The A- range is from 90 to 93.33, and the C+ range is 76.66 to 79.99. So, an A- would equal 91.67, and a C+ would = 78.33. =============================== Here's more info on what I have right now... There are columns in which numeric point values only are entered for assignment scores. For instance: Student X AA13 = 20 Student Y AA14 = 17 Student Z AA15 = <blank because the student had an excused absence In cell AA10, the max point value of 20 was entered for that assignment. Assignments were added to the right of col. AA almost daily. Note that if a teacher used letter grades instead of point values, the max values in row 10 would not be needed<< Scores were averaged across a quarter. So, the following formulas calculated the points possible, average score, and a % based off of assignment points: For Student X... P13 = SUMIF(AA13:AX13,"0",AA$10:AX$10) Q13 =SUM(AA13:AX13) R13 =AVERAGE(IF(($AA13:AX13<"")*($AA$10:AX$10<0),$AA 13:AX13/$AA $10:AX$10))*100 In determining final letter grades, I did some weighting with some other scores, but ultimately used this formula in col. X: =IF(V13<"",HLOOKUP(V13/100,Hgrades,2),"") So, there were 2 cols. in a sheet named Fields that the HLOOKUP referenced: 0 N 40 I 50 F 60 D- 63.33 D 66.67 D+ 70 C- 73.33 C 76.67 C+ (In the above, a C+ would = 78.33) 80 B- (In the above, a B- would = 71.67) 83.33 B (In the above, a B would = 85.00) 86.67 B+ (In the above, a B+ would = 88.33) 90 A- (In the above, an A- would equal 91.67) 93.33 A 96.67 A+ =============================== Very curious...Thanks a lot! Arnold |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can Text be Treated as Values for use in Calculations with Numbers
Thanks for responding Tom,
My mistake on the statement "2 cols. in a sheet named Fields that the HLOOKUP referenced"--The HLOOKUP is horizontal, occupies 15 columns, is =Fields!$J$1:$X$4, and now looks something like: % Score 0.00% 40.00% 50.00% 60.00% 63.33% 66.67% 70.00% Ltr Grade NC INC F D- D D+ C- GPA 0.00 0.00 0.33 0.67 1.00 1.33 1.67 Average 0.00 45.00 55.00 61.67 65.00 68.33 71.67 and so on... with the mid points of the grade ranges now put on row 4. However, col. V on the grades sheet is dependent on the values in cols. P, Q, and R, and it would need to be these cols. that could deal with the mix of letters and number scores in calculations. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can Text be Treated as Values for use in Calculations with Num
I guess that would make sense since you were using HLOOKUP
=IF(I7="","",IF(ISNUMBER(I7),HLOOKUP(I7/100,Fields!$J$1:$X$4,2,TRUE),HLOOKUP(I7,Fields!$J$ 2:$X$4,3,FALSE))) when I entered 45 in I7, i returned INC when I entered D- in I7 it retuned 61.67 which is what I expected. -- Regards, Tom Ogilvy "Arnold" wrote: Thanks for responding Tom, My mistake on the statement "2 cols. in a sheet named Fields that the HLOOKUP referenced"--The HLOOKUP is horizontal, occupies 15 columns, is =Fields!$J$1:$X$4, and now looks something like: % Score 0.00% 40.00% 50.00% 60.00% 63.33% 66.67% 70.00% Ltr Grade NC INC F D- D D+ C- GPA 0.00 0.00 0.33 0.67 1.00 1.33 1.67 Average 0.00 45.00 55.00 61.67 65.00 68.33 71.67 and so on... with the mid points of the grade ranges now put on row 4. However, col. V on the grades sheet is dependent on the values in cols. P, Q, and R, and it would need to be these cols. that could deal with the mix of letters and number scores in calculations. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vlookup formula editing based on numbers treated as text | Excel Worksheet Functions | |||
Text treated as a numeric value? | Excel Discussion (Misc queries) | |||
Numbers treated as text | Excel Programming | |||
Numbers stored as text prevent calculations | Excel Worksheet Functions | |||
how to make bmp or jpgs graphics be treated as cell values | Excel Discussion (Misc queries) |