Converting calculation to a grade

[Krayna]

Hi, I am quite new to VB in Excel.
I would like to write a function which converts any given percentage into a grade following the grade system defined (this would vary depending on class so would I define table first?)
I have tried mathematically working this out using ratios/percentages but could not think of a straightforward method to do this...
Would appreciate any ideas.

Sample conversion rates:
0 - 0%
1.0 - 45%
1.6 - 65%
2.3 - 75%
3.1 - 85%
3.7 - 100%

 

[Eric W]

Welcome to the forum!

You can do this on the worksheet like this:

Book1
	D	E	F	G	H	I
1	Percent	Grade	Grade		Percent	Grade
2	0%	0.0	0		0%	0
3	33%	0.0	0		45%	1
4	64%	1.0	1		65%	1.6
5	65%	1.6	1.6		75%	2.3
6	66%	1.6	1.6		85%	3.1
7	92%	3.1	3.1		100%	3.7
8	100%	3.7	3.7
Sheet3

Cell Formulas
Range	Formula
E2:E8	E2	=LOOKUP(D2,{0,0.45,0.65,0.75,0.85,1},{0,1,1.6,2.3,3.1,3.7})
F2:F8	F2	=VLOOKUP(D2,$H$2:$I$7,2)

The E2 formula has the table built into it, the F2 formula uses the H1:I7 table, so you can change it as desired.

If you're looking for a VBA solution, you can use either of these formulas in the EVALUATE function. There are also native VBA methods you can use. Let us know exactly what you're looking for.

 

[Krayna]

Okay that does solve part of my query.
However the grades to be converted must find the grade to the closest decimal point the ones I provided are a guide of the boundaries.
For example, 55% would be 1.3

How to implement this on spreadsheet?

 

[KRice]

Here is one idea. The FORECAST/OFFSET part gives an error when the input percentage matches the last lookup entry. To fix this, I added an initial check in the formula, but there may be a more elegant way.

Book1
	D	E	F	G	H	I
1	Percent	Grade			Percent	Grade
2	0	0.00			0	0
3	0.33	0.73			0.45	1
4	0.64	1.57			0.65	1.6
5	0.65	1.60			0.75	2.3
6	0.66	1.67			0.85	3.1
7	0.92	3.38			1	3.7
8	1	3.70
9	0.55	1.30
Krayn

Cell Formulas
Range	Formula
E2:E9	E2	=IF(D2=$H$7,$I$7,FORECAST(D2,OFFSET($I$2:$I$7,MATCH(D2,$H$2:$H$7,1)-1,0,2), OFFSET($H$2:$H$7,MATCH(D2,$H$2:$H$7,1)-1,0,2)))

 

[Krayna]

Super. Thanks a mill for your help

 

[Krayna]

Would IFERROR do the check you did?

 

[Eric W]

Sure, you could use:

=IFERROR(FORECAST(D2,OFFSET($I$2:$I$7,MATCH(D2,$H$2:$H$7,1)-1,0,2), OFFSET($H$2:$H$7,MATCH(D2,$H$2:$H$7,1)-1,0,2)),$I$7)

but it's sort of a wash as to which is better. Kirk's version might be better because if you have a non-numeric value in D2, his would create an actual error code, but the IFERROR version would just put the maximum value in it.

 

[KRice]

Sorry...I missed the notification from earlier today. Eric is spot on. You could use the IFERROR wrapping, but be aware of its behavior. I wasn't sure how you wanted to treat: 1) blank cells (giving a result of 0), 2) errant text inputs (resulting in a #VALUE! error), and 3) errant values that fall outside the range specified in the lookup table (producing either a #N/A or #DIV/0! error). If you're reasonably confident that these things won't become issues and the only likely snag might be when the input percentage matches the upper bound of the lookup table, then the IFERROR wrapping shown by Eric might be preferred.