Special Average Needed

[Karel12]

Hi everyone,

Would someone be able to help me in finding a solution to a problem.

I have rows of data, which attainment grades are entered in alternate cells across the row, These are entered as text; Ex, G, S, C. I want to create an average cell for each row/student, that will calculate the average of these, showing the most commonly occuring attainment grade. I dont mind if its the mode or mean.

Is this possible?

Many Thanks in Advance

K

 

[Karel12]

I have thought about using A IF statement containing CountIF formulae,

so if I say that the number of Ex's is > than the number of C's or S's or G's, then display Ex in the average cell.

I have tried to write this formula but am stuck after the first hurdle, please could someone help.

Regards
K

 

[Dryver14]

This should get you started

Excel Workbook
	C	D	E	F	G	H
6
7				s	t	g
8	s	s	s	0.56	0.29	0.20
9	s	t	s
10	t	g	b
11
Sheet1

 

[Karel12]

This is very different to what I planned, I need just one cell to display the most commonly entered attainment grade in it.

I need to reference the specific letters in the row, Ex, G, C, S, as they are entered between alternate columns within which are numbers for effort grades
So it could read

|Ex|1|G|1|C|4| etc...

across the sheet

It needs to pick out the specific entered text.

I was thinking along the lines of
=IF((COUNTIF(L3:HB3,"Ex"))>(COUNTIF(L3:HB3,"G")),(Ex),(G))
but i want to extend this to include the other two conditions and display the most common.

Regards
K

 

[Dryver14]

this is the best I can do I am afraid, I started with If but dont know what to do in case of a tie any ideas?

Excel Workbook
	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
2												EX	C	S	G
3	EX	C	S	G	C	C	C	H	EX	G	G	2	4	1	3
Sheet2

 

[Ron Coderre]

Perhaps something like this....
Both formulas return the most occurring text value in the referenced range.

REGULAR FORMULA (completed by just pressing ENTER after editing)
=INDEX(L3:HB3,MODE(ISNUMBER(1/(L3:HB3&1))*COLUMN(L3:HB3)*1000+MATCH(L3:HB3&"",L3:HB3&"",0)))

ARRAY FORMULA (completed by pressing CTRL+SHIFT+ENTER, instead of just ENTER)
=INDEX(L3:HB3,MODE(IF(ISERROR(1/(L3:HB3&1)),MATCH(L3:HB3,L3:HB3,0))))

Is that something you can work with?

 

[Karel12]

It worked!!!!

Thank you so much Ron

 

[Ron Coderre]

You're very welcome...I'm glad I could help.

 

[Karel12]

Would it be possible to reproduce this formula to find the average of these for the column of averages that has been previously created?
Thereby finding the class average of the average marks.

Not sure what to change in the formula that was given.

Regards
K

 

[Ron Coderre]

I'm not sure I understand what you mean...Can you post some sample data and the results you want to see, based on that data?