Average grade letter (with a trick!)

Hi everyone,

I have a spreadsheet where I record the number of routes I have climbed. This allows me to keep track but more importantly motivate me as I would like to push the numbers up.

However, because of physical form peaks and through and training cycles, the numbers can sometimes be volatile and thus don't give me an appropriate view of my overall performance (and hopefully improvement!).

I would thus like to find a formula to generate the average route grade I climbed during a given month, in order to get a higher level picture.

The trick: the routes are recorded as a number but I would like to return a letter grade.

Here is the table attached.

grade table.png

Would anyone have an idea on how to do it ?

Thanks a lot,

Carlito

Attach the spreadsheet itself. We can't work with a picture of it. Thanks!

OK, so where am I to look? Have you manually mocked up what you want, and if so, where will I find it?

ie: Jan 2018: 5b, Feb 21018: 5c+...

How are you manually calculating these results? You are not telling us what we need to know.

OK, so you have now clarified what the numbers under the months mean - this was not clear (to me) before. It seems to me that you probably need a combination of FREQUENCY and SUMPRODUCT to do what you want. I am short of time now, but hopefully someone else will pick this up. There WILL be a way to do it, that's for sure.

On reflection, it can be done easily with a helper column (that could be hidden):

Excel 2016 (Windows) 32 bit

	B	C	D	E	F	G	H
2	Grade/date	Jan-18	Feb-18	Mar-18	Apr-18		Value
3	4a	4	-	5	3		1
4	4b	-	-	1	2		2
5	4c	4	1	-	2		3
6	5a	3	2	4	5		4
7	5a+	4	1	3	1		5
8	5b	4	3	4	3		6
9	5b+	5	-	2	1		7
10	5c	4	3	6	5		8
11	5c+	5	1	4	5		9
12	6a	4	5	8	12		10
13	6a+	5	2	2	4		11
14	6b	2	2	1	3		12
15	6b+	-	1	-	-		13
16	6c	-	-	-	-		14
17	6c+	-	-	-	-		15
18	Total	44	21	40	46
19
20		5b	5c	5b	5b+

Sheet: Sheet1

Excel 2016 (Windows) 32 bit

	C
20	=IFERROR(INDEX($B$3:$B$17,MATCH(SUMPRODUCT(C$3:C$17,$H$3:$H$17)/C$18,$H$3:$H$17,1)),"")

Sheet: Sheet1

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

No problem.