Hiya,

This is probably a walk in the park for most of you but I'm stumped!

I have a spreadsheet which analyses reference requests. In the character
section, feedback is requested on 10 items. Each item can be graded "good",
"satisfactory" or "needs improving". On my spreadsheet I want a sum to
auto-input the majority answer.

The data looks like this:

G H I J
Good Satisfactory Needs Improving Majority
5 10 0 0
6 4 6 0
7 2 1 7
8 5 5 0

I need a formula for column J. I also have the problem that sometimes (as in
row 8) there can be identical numbers for different levels of performance.

I have been playing about with IFs but have yet to come up with something
functional. To be honest, I'm not sure whether I'm even using the right
function for something like this.

Any help would be greatly appreciated!

Thank you.

do you want a sum or you want Col J to return the highest value?
if it is the latter, use =MAX(G5:I5)
if you want a total, simply do a =SUM(range:range), but I don't understand
your requirement
if you need this as there's no good data for analysis.

Let me know if this work for you, if not, post with a sample of result you
want
--
HTH

Pls provide your feedback by clicking the YES button below if this posting
is helpful
This will help others to search the results in the archive better

cheers, francis

"Wendy-Bob" wrote in message
...
Hiya,

This is probably a walk in the park for most of you but I'm stumped!

I have a spreadsheet which analyses reference requests. In the character
section, feedback is requested on 10 items. Each item can be graded
"good",
"satisfactory" or "needs improving". On my spreadsheet I want a sum to
auto-input the majority answer.

The data looks like this:

G H I J
Good Satisfactory Needs Improving Majority
5 10 0 0
6 4 6 0
7 2 1 7
8 5 5 0

I need a formula for column J. I also have the problem that sometimes (as
in
row 8) there can be identical numbers for different levels of performance.

I have been playing about with IFs but have yet to come up with something
functional. To be honest, I'm not sure whether I'm even using the right
function for something like this.

Any help would be greatly appreciated!

Thank you.

Assuming performance col headers are in G1:I1,
with data from row2 down
In J2: =INDEX(G$1:I$1,MATCH(MAX(G2:I2),G2:I2,0))
Copy down. In the event of a tie/s in the max score, only the leftmost col
header will be returned.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik
---
"Wendy-Bob" wrote:
I have a spreadsheet which analyses reference requests. In the character
section, feedback is requested on 10 items. Each item can be graded "good",
"satisfactory" or "needs improving". On my spreadsheet I want a sum to
auto-input the majority answer.

The data looks like this:

G H I J
Good Satisfactory Needs Improving Majority
5 10 0 0
6 4 6 0
7 2 1 7
8 5 5 0

I need a formula for column J. I also have the problem that sometimes (as in
row 8) there can be identical numbers for different levels of performance.

I have been playing about with IFs but have yet to come up with something
functional. To be honest, I'm not sure whether I'm even using the right
function for something like this.

Any help would be greatly appreciated!

Thank you.

Thanks Max, that's working perfectly.

The titles are in Row 4 but I've adjusted to reflect this.

Now this is working, is it possible to use the INDEX and MAX functions to
identify the most frequent high scorer?

So (presuming all other data remains unchanged)...

J
Majority (row 4)
5 Good
6 Good
7 Needs Improving
8 Good
9

So cell J9 would count the instances of each answer and enter the most
frequent one (ie. there are 3 goods and 1 needs improving, so the cell would
say Good).

Using a rather longwinded COUNTIF and MAX I can get the number of Goods but
I can't get the indexing to work so it actually says Good (not just 3)

Hope that makes sense...

-Wendy


"Max" wrote:

Assuming performance col headers are in G1:I1,
with data from row2 down
In J2: =INDEX(G$1:I$1,MATCH(MAX(G2:I2),G2:I2,0))
Copy down. In the event of a tie/s in the max score, only the leftmost col
header will be returned.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik
---
"Wendy-Bob" wrote:
I have a spreadsheet which analyses reference requests. In the character
section, feedback is requested on 10 items. Each item can be graded "good",
"satisfactory" or "needs improving". On my spreadsheet I want a sum to
auto-input the majority answer.

The data looks like this:

G H I J
Good Satisfactory Needs Improving Majority
5 10 0 0
6 4 6 0
7 2 1 7
8 5 5 0

I need a formula for column J. I also have the problem that sometimes (as in
row 8) there can be identical numbers for different levels of performance.

I have been playing about with IFs but have yet to come up with something
functional. To be honest, I'm not sure whether I'm even using the right
function for something like this.

Any help would be greatly appreciated!

Thank you.

Try this array formula which need to confirm by Ctrl, Shift and Enter together
otherwise it will give an incorrect result. There will be {....} wrap around
the formula if you look into the formula bar.

=INDEX(J4:J8,MATCH(MAX(COUNTIF(J4:J8,J4:J8)),COUNT IF(J4:J8,J4:J8),0))

Hope this help.

--
Pls provide your feedback by clicking the Yes button below if this post have
help you. This will help others to search the archives for result better.


Thank You

cheers, francis



"Wendy-Bob" wrote:

Thanks Max, that's working perfectly.

The titles are in Row 4 but I've adjusted to reflect this.

Now this is working, is it possible to use the INDEX and MAX functions to
identify the most frequent high scorer?

So (presuming all other data remains unchanged)...

J
Majority (row 4)
5 Good
6 Good
7 Needs Improving
8 Good
9

So cell J9 would count the instances of each answer and enter the most
frequent one (ie. there are 3 goods and 1 needs improving, so the cell would
say Good).

Using a rather longwinded COUNTIF and MAX I can get the number of Goods but
I can't get the indexing to work so it actually says Good (not just 3)

Hope that makes sense...

-Wendy


"Max" wrote:

Assuming performance col headers are in G1:I1,
with data from row2 down
In J2: =INDEX(G$1:I$1,MATCH(MAX(G2:I2),G2:I2,0))
Copy down. In the event of a tie/s in the max score, only the leftmost col
header will be returned.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik
---
"Wendy-Bob" wrote:
I have a spreadsheet which analyses reference requests. In the character
section, feedback is requested on 10 items. Each item can be graded "good",
"satisfactory" or "needs improving". On my spreadsheet I want a sum to
auto-input the majority answer.

The data looks like this:

G H I J
Good Satisfactory Needs Improving Majority
5 10 0 0
6 4 6 0
7 2 1 7
8 5 5 0

I need a formula for column J. I also have the problem that sometimes (as in
row 8) there can be identical numbers for different levels of performance.

I have been playing about with IFs but have yet to come up with something
functional. To be honest, I'm not sure whether I'm even using the right
function for something like this.

Any help would be greatly appreciated!

Thank you.