Calculate the maximum TEXT values in a range, but exclude one value?

**kpratico** · 03-19-2010, 11:51 AM

I have a spreadsheet where I categorized task status with Red/Yellow/Green/a (a = a check mark when formatted in Webdings font). I would like to caluclate the maximum text range in a value, but I want to exclude any cells with "a" as the value. Found the following formula on a separate post, which calculates the max text value in a range, but I have no idea how to exclude the text value of "a"

So in the following example, I'd like the formula to return "green"

A36 = red
A37 = green
A38 = green
A39 = a
A40 = a
A41 = a

**DonkeyOte** · 03-19-2010, 12:09 PM

Using your ranges - perhaps:

=INDEX(A36:A41,MODE(IF(A36:A41<>"a",MATCH(A36:A41,A36:A41,0))))
confirmed with CTRL + SHIFT + ENTER

**daddylonglegs** · 03-19-2010, 12:09 PM

Try this array formula confirmed with CTRL+SHIFT+ENTER

=INDEX(A36:A41,MODE(IF(A36:A41<>"a",MATCH(A36:A41,A36:A41,0))))

**Marcol** · 03-19-2010, 12:17 PM

Your soultion might be, using your post conditions for A36 > A41
Put in Cell B41

=COUNTIF($A$36:$A$41,"<>a")

EDIT:

Why does my browser not update with the refresh button?

I didn't see these posts,nor did I read the question correctly!

As a peace offering

In say B41 as per DonkeyOte and daddylonglegs

=INDEX(A36:A41,MODE(IF(A36:A41<>"a",MATCH(A36:A41,A36:A41,0))))
confirmed with CTRL + SHIFT + ENTER

In C41

=COUNTIF(A36:A41,B41)

And you have the number of incidences of "green" as a bonus!

**kpratico** · 03-19-2010, 01:26 PM

DonkeyOte and Daddylonglegs - that worked perfectly! Thank you so much for your help, and to all for the quick responses!

**kpratico** · 03-19-2010, 02:41 PM

Me again . . .

So that formula worked great, until I tried to use it against cells in a new spreadsheet. I need to use it for various ranges. Also, the cell ranges are using data validation lists - not sure if that makes a difference? Here is an example - once I hit CTRL+SHIFT+ENTER, I get #N/A no matter what the value of my range cells is.

=INDEX(AA9:AA16,MODE(IF(AA9:AA16<>"a",MATCH(AA9:AA16,AA9:AA16,0))))

**kpratico** · 03-19-2010, 05:00 PM

Here is an example of the issue I keep running into. I will have up to 30 columns in a spreadsheet where I'd like to use this formula. For each, the range may vary in size. As you can see, the formula is not working for either team.

Thanks in advance for anyone who can help!

KP