Results 1 to 14 of 14
  1. #1
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    405
    Thanks
    35
    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 "".

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,815
    Thanks
    132
    Thanked 480 Times in 457 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
    Attached Files Attached Files

  3. #3
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    405
    Thanks
    35
    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.

  4. #4
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    405
    Thanks
    35
    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.

  5. #5
    WS Lounge VIP sdckapr's Avatar
    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.
    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
    Last edited by sdckapr; 2012-05-26 at 06:18.

  6. #6
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    405
    Thanks
    35
    Thanked 5 Times in 5 Posts

    Post

    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.

  7. #7
    WS Lounge VIP sdckapr's Avatar
    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

  8. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,815
    Thanks
    132
    Thanked 480 Times in 457 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
    Attached Files Attached Files

  9. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 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

  10. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 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

  11. #11
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,815
    Thanks
    132
    Thanked 480 Times in 457 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

  12. #12
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 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

  13. #13
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,815
    Thanks
    132
    Thanked 480 Times in 457 Posts
    Hi Rory

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

    zeddy

  14. #14
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •