Return The Majority Value

**sinspawn56** · 10-06-2009, 06:00 PM

Hello. I need to create a formula that will look at a row of text values and return the value that is the greatest majority. I have attached a sheet that emulates my issue.

**Palmetto** · 10-06-2009, 06:17 PM

Use this array formula:

=INDEX($B$2:$H$2,MODE(MATCH($B$2:$H$2,$B$2:$H$2,0)))

You must commit the formula by pressing Ctrl+Alt+ Enter keys together

**sinspawn56** · 10-06-2009, 06:30 PM

Ok, I forgot to take into account that I have 2 hidden rows between each row that has the values. I think the array you had me enter is looking at the hidden values as well. Is there a way to just select the cells that have the values?

**daddylonglegs** · 10-06-2009, 06:43 PM

In your example you show data in a single row....but you say you have hidden rows, is your data really in a column (with hidden rows).....or a row (with hidden columns)?

**sinspawn56** · 10-06-2009, 06:49 PM

Sorry. I have hidden columns not rows. I want to return the majority in the single row, but I have data in column b (c,d are hidden) e (f,g are hidden) and the same row etc

**daddylonglegs** · 10-06-2009, 06:58 PM

For the modal value for every 3rd column in B2:Z2, i.e. B2,E2,H2,K2,N2,Q2...etc try adjusting Palmetto's suggestion like this

=INDEX(B2:Z2,MODE(IF(MOD(COLUMN(B2:Z2)-COLUMN(B2),3)=0,IF(B2:Z2<>"",MATCH(B2:Z2,B2:Z2,0)))))

confirmed with CTRL+SHIFT+ENTER

Adjust ranges as required

If there are no text values that appear more than once you get #N/A error