Formula

[mad3]

I have a scoresheet that has letter grades in column B and A NUMBER FROM -4 TO +4 IN column C. I want to adjust the letter grades based on the number in column C. With the new letter grade in column D which is called ADJ.

[TABLE="width: 453"]
<tbody>[TR]
[TD]Team[/TD]
[TD]OFF[/TD]
[TD]PIT/DEF[/TD]
[TD]ADJ[/TD]
[TD]HM[/TD]
[TD]Pressure[/TD]
[/TR]
[TR]
[TD]Red Sox[/TD]
[TD]Q[/TD]
[TD]-2[/TD]
[TD][/TD]
[TD][/TD]
[TD]K[/TD]
[/TR]
[TR]
[TD]Dodgers[/TD]
[TD]Q[/TD]
[TD]-4[/TD]
[TD][/TD]
[TD][/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Rays[/TD]
[TD]O[/TD]
[TD]-1[/TD]
[TD][/TD]
[TD][/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]Rangers[/TD]
[TD]Q[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD]F[/TD]
[/TR]
</tbody>[/TABLE]


Help in California...

 

[jtakw]

Hi,

You didn't say How the Letter grade is to be adjusted by the Number.

 

[mad3]

Hi,

You didn't say How the Letter grade is to be adjusted by the Number.

My bad... If the number in column 'C' is a positive, the letter grade advances to R...S...T and so on. If the number is a negative, it goes backwards ... P...O...N etc.

 

[jtakw]

Here you go:

Excel 2010
	A	B	C	D	E	F
1	Team	OFF	PIT/DEF	ADJ	HM	Pressure
2	Red Sox	Q	-2	O		K
3	Dodgers	Q	-4	M		N
4	Rays	O	-1	N		G
5	Rangers	Q	2	S		F
Sheet1

Cell Formulas
Range	Formula
D2	=CHAR(SUM(CODE(B2),C2))

ADJ column formula copied down.

 

[mad3]

Here you go:

Excel 2010

	A	B	C	D	E	F
Team	OFF	PIT/DEF	ADJ	HM	Pressure
Red Sox	Q	O	K
Dodgers	Q	M	N
Rays	O	N	G
Rangers	Q	S	F

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]-2[/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]-4[/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]-1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]2[/TD]

[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D2[/TH]
[TD="align: left"]=CHAR(SUM(CODE(B2),C2))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



ADJ column formula copied down.

Worked perfectly. Now on to the next step. Thanks so much!

 

[jtakw]

You're welcome.