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

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
[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,895
Messages
6,175,257
Members
452,625
Latest member
saadat28

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