I have a list of names like the following within group:
Western Group 1
John = 2
Charlie = 3
Hatty = 7
Pete = 4
Western Group 2
Jane = 6
Peter = 5
Dave = 4
Lisa = 4
Eastern Group 1
Dae = 1
Awa = 1
Flad = 6
Daw = 3
Eastern Group 2
Feda = 2
Caveer = 6
The number of groups I can have vary.
My Forumla is like this:
Average Western Group = ((Total of Group 1 / Number of names within group 1) + (Total of Group 2 / Number of names within group 2)) / Number of groups
E.g. in the sample data I've provided it would output the following:
Average Western Group = ((2+3+7+4 / 4) + (6+5+4+4 / 4))/ 2
Then do the same again for the Eastern Groups.
The required value is the median value between Average Western Group and Average Eastern Group like so:
Required Value = (Average Western Group - Average Eastern Group) / 2 + Average Eastern Group
The issue I'm having is that I need a single mathematical equation where I won't know how many groups there will be and I won't know how many names there will be in a group.
I do realise this question is a little odd but is there a way to put this into a single real mathematical equation? I'm wrecking my brain over this. I'm sure its simple but I seem to have hit a brick wall at the moment.