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

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

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

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

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))))

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. 🙂