“Average” a Range of Strings (Like They Were Numbers)

Topic

New Reply

I want a formula using functions that I can put into a cell (I have much bigger ranges than in the examples below). If I had 3, ,4, ,5 in cells A1:A5, and I asked Excel to average them in B1, it would return 4 (by ignoring the blanks). What I have is ABEL,””,ABEL,””,ABEL in cells A1:A5. Note that two of the cells are empty strings. I want to “average” them in the sense that Excel would return ABEL as my answer. So, an answer to this problem would need to: 1) Return ABEL as the “average” for a range that has at least one ABEL in it, and any number of empty strings like “”, 2) Return an empty cell or “” as the “average” for any range that has no ABEL in it at all, and 3) Return something recognizable as an error (that I can filter) if I try to “average” a range that contains anything other than ABEL or empty strings like “”.

Reply | Quote

Replies

Hi

OK, lets forget about ‘average’ for a moment and break down what you seem to be asking for:
1. If you have a range that contains any cells with anything other than “” or “ABEL”, show an error message.
2. Otherwise, If your range contains at least one cell with “ABEL”, then the formula returns “ABEL”
3. Otherwise, if the range only contains “”, then the formula returns “”.

So, essentially, what we need to do is:
1. Find out how many cells there are in the range, e.g. X
2. Find out how many cells are =””, e.g. Y
3. Find out how many cells are =”ABEL”, e.g. Z

Then, IF X = Y + Z, then there are no other cell values, i.e. no error message required,
and if Z > 0 then you want “ABEL”.

So, if your data range was say, A1:A25, youcould use this formula:
=IF((ROWS(A1:A25)-(COUNTIF(A1:A25,”ABEL”)+COUNTIF(A1:A25,””)))>0,”range contains unexpected data”,IF(COUNTIF(A1:A25,”ABEL”)>0,”ABEL”,””))

..adjust range as required.

see attached workbook

zeddy

Reply | Quote

This is a good approach to my problem …

But it runs into trouble if I don’t know what the text is going to be in a range.

So I need a formula that will look at a range, determine that it contains empty strings and one or more cells with “Abel” and return “Abel” — which your’s does — but be copyable to the next range where the string will be “Baker” and return “Baker”. But, if it’s copied to another cell, and the range its formula operates on contains “Charlie” and “Delta” it will return the error message.

Reply | Quote

P.S. Sorry if my “average” metaphor didn’t work well.

What I meant was that I want something that would operate on 2, ,2, ,2 and return the average as 2.0 and operate on 2, ,2, ,3 and return 2.3. That would be easy to filter … by formula but also visually. I’m looking for the same sort of functionality on text strings.

Reply | Quote

So I need a formula that will look at a range, determine that it contains empty strings and one or more cells with “Abel” and return “Abel” — which your’s does — but be copyable to the next range where the string will be “Baker” and return “Baker”. But, if it’s copied to another cell, and the range its formula operates on contains “Charlie” and “Delta” it will return the error message.

You could put the “target value” of each row in a cell in that row and then use a formula which looks it up

What I meant was that I want something that would operate on 2, ,2, ,2 and return the average as 2.0 and operate on 2, ,2, ,3 and return 2.3.

But in your analogy you would get 2 for 2,2,2 but an error message for 2,2,3… so sorting would do not good: 2,2,3 and 2,2,4 and 3,3,4 and 5,5,10 would ALL give the same response of “error”

If you truly want an “average” why not put every possible text string you anticipate and assign it a value. Then have intermediate cells convert each string to its value and then average the converted values…

Steve

Reply | Quote

Thanks Steve.

I am talking about 2 different ideas in my query: average and “average”. This is why I keep using double quotes.

I sometimes use averaging as an error trap (other formulas too). In my example, doing an average on 5 cells (2, ,2, ,2) would yield 2, but doing it on (2, ,2, ,3) would yield 2.33 in general format. For a novice that would stand out as something different because of the different number of digits shown.

I want the same thing for a range of text. Thus an error trap that works like an average: thus, an “average”. I want:

“”,””,”” to yield “”
Abel,””,”” to yield Abel
“”,Baker,Baker to yield Baker
Charlie,Delta,”” to yield an error some error code (that I’ll tailor so as not to scare my user)

If I had a similar pattern with numbers (and blank cells instead of empty strings), and used =average() as my error trap, it would work like this:

, , to yield a blank cell
2, , to yield 2
,3,3 to yield 3
4,5, to yield 4.5

The great thing about using =average() as an error trap is that it works readily when copied.

Zeddy’s suggestion is very close to doing this for text … but it won’t copy readily because Abel is embedded in the formula that will be copied. I’d like to add the functionality to detect the string that is repeated in the range to Zeddy’s code that can report if there is a problem.

Reply | Quote

Hi

It would be easier, as Steve shows, if you already know what it is you are looking for.

However, on the basis you don’t know what you are searching for, i.e. it might not be “ABEL”, it could be any multiple occurence of some unknown text, like “FRED”, “RICHARD”,”ZEDDY” etc, then perhaps the attached solution is what you are looking for.

zeddy

Reply | Quote

Something like this will work if col A has the value you want them all to match:
=IF((COUNTA(A1:F1)-(COUNTIF(A1:F1,A1)+COUNTIF(A1:F1,””)))>0,”range contains unexpected data”,IF(COUNTIF(A1:F1,A1)>0,A1,””))

Steve

Reply | Quote

Zeddy,
You can’t use CONCATENATE on a range of cells like that. Try deleting the value in A8 and see what happens…

Reply | Quote

Hi rory

Yes, it does seem you can’t. Mostly.
But if you delete the value in A8, it still shows the answer in cell [E19].
And if you enter the formula =CONCATENATE(A1:A25) into another cell, it still returns JACK.
Instead of deleting cell [A8], if you type in FRED, you get the required message “range contains unexpected data”.
If you continue down and enter FRED for each cell that contained Jack, then when you enter the last FRED the formula changes from “range contains unexpected data” to the required “FRED”.

zeddy

Reply | Quote

You could use something like
=IF(SUMPRODUCT(1/COUNTIF($A$1:$A$25,$A$1:$A$25&””))-(COUNTIF($A$1:$A$25,””)>0)>1,”Too many different items”,LOOKUP(REPT(“Z”,255),CHOOSE({1,2},””,LOOKUP(REPT(“Z”,255),$A$1:$A$25))))

Reply | Quote

=CONCATENATE(A1:A25) is equivalent to =A1:A25
it returns an array of the cell values with no concatenation at all and what it returns to one cell will depend on how and where you enter it:
1. If you array enter it:
-in one cell and then copy or fill that anywhere, you will get the first entry
-in multiple cells, you will get the respective entries 1:n depending on how many cells you array enter into
2. if you enter it normally:
– in a cell in rows 1:25, you will get the item for that row
– in any other row you will get a #VALUE! error.

Reply | Quote

Hi Rory

You are absolutely correct.
Never did like to use CONCATENATE anyway.
Your formula is the one to use.

zeddy

Reply | Quote

CONCATENATE will never be truly useful until MS finally listen to reason and make a version that accepts one range as an argument rather than individual cells (preferably with an optional delimiter). Until that time I will always use ‘&’ instead as it’s easier to spell.

Reply | Quote