Question about memory usage in excel

lars1565

New Member
Joined
Apr 18, 2016
Messages
10
Office Version
  1. 2019
Platform
  1. Windows
I have a grade book for a school. It is shared online for teachers and staff to use/edit. Due to the number of formulas it can get kind of bogged down. So I am looking to save some processing power and accomplish the same task.
A teacher enters a percent in cell then the cell below has a large formula to calculate the letter grade associated with that percent. So each cell has a big formula for each student and each of their classes.
I am wondering if I switch from: =IF(F1405>=0.93,"A",IF(F1405>=0.9,"A-",IF(F1405>=0.87,"B+",IF(F1405>=0.83,"B",IF(F1405>=0.8,"B-",IF(F1405>=0.77,"C+",IF(F1405>=0.73,"C",IF(F1405>=0.7,"C-",IF(F1405>=0.67,"D+",IF(F1405>=0.63,"D",IF(F1405>=0.6,"D-",IF(F1405="","","N"))))))))))
to: a short lookup() formula with a table that has percent and associated grades. Would that use less memory and run faster?

Thanks
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]
A​
[/td][td="bgcolor:#C0C0C0"]
B​
[/td][td="bgcolor:#C0C0C0"]
C​
[/td][/tr][tr][td="bgcolor:#C0C0C0"]
1​
[/td][td="bgcolor:#F3F3F3"]
Score
[/td][td="bgcolor:#F3F3F3"]
Grade
[/td][td="bgcolor:#F3F3F3"]
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
2​
[/td][td]
95%​
[/td][td="bgcolor:#CCFFCC"]A[/td][td="bgcolor:#CCFFCC"]B2: =IF(A2="", "", LOOKUP(A2, {0,60,63,67,70,73,77,80,83,87,90,93}%, {"N","D-","D","D+","C-","C","C+","B-","B","B+","A-","A"}))[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
3​
[/td][td]
92%​
[/td][td="bgcolor:#CCFFCC"]A-[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
4​
[/td][td]
87%​
[/td][td="bgcolor:#CCFFCC"]B+[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
5​
[/td][td]
86%​
[/td][td="bgcolor:#CCFFCC"]B[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
6​
[/td][td]
81%​
[/td][td="bgcolor:#CCFFCC"]B-[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
7​
[/td][td]
78%​
[/td][td="bgcolor:#CCFFCC"]C+[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
8​
[/td][td]
72%​
[/td][td="bgcolor:#CCFFCC"]C-[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
9​
[/td][td]
68%​
[/td][td="bgcolor:#CCFFCC"]D+[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
10​
[/td][td]
64%​
[/td][td="bgcolor:#CCFFCC"]D[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
11​
[/td][td]
62%​
[/td][td="bgcolor:#CCFFCC"]D-[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
12​
[/td][td]
59%​
[/td][td="bgcolor:#CCFFCC"]N[/td][td][/td][/tr]
[/table]
 
Upvote 0
The following is just shg's suggestion with a table for the Lookup.


Excel 2010
ABCD
1ScoreGradeGradeGrade
295%AAA
4b
Cell Formulas
RangeFormula
B2=IF(A2="", "", LOOKUP(A2, {0,60,63,67,70,73,77,80,83,87,90,93}%, {"N","D-","D","D+","C-","C","C+","B-","B","B+","A-","A"}))
C2=LOOKUP(A2,$E$1:$F$12)
D2=LOOKUP(A2,rGrades)
Named Ranges
NameRefers ToCells
rGrades='4b'!$E$1:$F$12
 
Upvote 0
Your current formula and all of the alternatives should be lightning fast. I'd be inclined to use Dave's suggestion to put the lookup data in a table -- just be aware that you you change the table (like you decide to start grading on a curve), it will change all of the grades, some perhaps unintendedly.
 
Upvote 0
Hopefully, the suggested formula helps.

Once the grades are calculated, the information may be static;
consequently, the formula results could be converted to values.

With about 1050 students, the file with the formulas is just 70k.
The file with all rows as values except the first row of calculations is just 54K.

Your file may include other calculations or statistics that are resource intensive.


Excel 2010
ABCDE
1NameScoreGradeGradeGrade
2A123395%AAA
3A123492%A-A-A-
Sheet1
Cell Formulas
RangeFormula
C2=LOOKUP(B2,$F$1:$G$12)
D2=LOOKUP(B2,rGrades)
E2=LOOKUP(B2,rGrades)
Named Ranges
NameRefers ToCells
rGrades=Sheet1!$F$1:$G$12
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,849
Members
452,361
Latest member
d3ad3y3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top