Results 1 to 14 of 14

20120524, 18:26 #1
 Join Date
 Jun 2003
 Location
 Utah, USA
 Posts
 425
 Thanks
 45
 Thanked 5 Times in 5 Posts
"Average" a Range of Strings (Like They Were Numbers)
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 "".

20120525, 07:28 #2
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 3,233
 Thanks
 154
 Thanked 609 Times in 579 Posts
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,"ra nge contains unexpected data",IF(COUNTIF(A1:A25,"ABEL")>0,"ABEL",""))
..adjust range as required.
see attached workbook
zeddy

20120525, 13:51 #3
 Join Date
 Jun 2003
 Location
 Utah, USA
 Posts
 425
 Thanks
 45
 Thanked 5 Times in 5 Posts
This Is Very Close
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.

20120525, 13:55 #4
 Join Date
 Jun 2003
 Location
 Utah, USA
 Posts
 425
 Thanks
 45
 Thanked 5 Times in 5 Posts
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.

20120525, 14:11 #5
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
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.
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.
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...
SteveLast edited by sdckapr; 20120526 at 05:18.

20120526, 01:06 #6
 Join Date
 Jun 2003
 Location
 Utah, USA
 Posts
 425
 Thanks
 45
 Thanked 5 Times in 5 Posts
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.

20120526, 05:25 #7
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
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

20120526, 09:16 #8
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 3,233
 Thanks
 154
 Thanked 609 Times in 579 Posts
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

20120528, 03:58 #9
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,322
 Thanks
 3
 Thanked 214 Times in 197 Posts
Zeddy,
You can't use CONCATENATE on a range of cells like that. Try deleting the value in A8 and see what happens...Regards,
Rory
Microsoft MVP  Excel

20120528, 04:13 #10
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,322
 Thanks
 3
 Thanked 214 Times in 197 Posts
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))))Regards,
Rory
Microsoft MVP  Excel

20120528, 05:11 #11
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 3,233
 Thanks
 154
 Thanked 609 Times in 579 Posts
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

20120528, 05:24 #12
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,322
 Thanks
 3
 Thanked 214 Times in 197 Posts
=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.
Regards,
Rory
Microsoft MVP  Excel

20120528, 05:33 #13
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 3,233
 Thanks
 154
 Thanked 609 Times in 579 Posts
Hi Rory
You are absolutely correct.
Never did like to use CONCATENATE anyway.
Your formula is the one to use.
zeddy

20120528, 06:36 #14
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,322
 Thanks
 3
 Thanked 214 Times in 197 Posts
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.
Regards,
Rory
Microsoft MVP  Excel