Results 1 to 8 of 8

Thread: average of non-numeric cells without helper column
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Oct 2009
    Location
    UK
    Posts
    324
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default average of non-numeric cells without helper column

    Hi there,

    I am trying to identify a mismatch based on non-numeric data in a spreadsheet but unable to do this without a helper column that converts it to a numerical value.

    It seems like there should be an easier way to achieve this (without the helper column) but I gave up searching for it!

    Any ideas?


    Current data as follows:

    Order Item Plant h f
    123 10 G013 13 check
    123 20 G120 120 check
    456 10 G013 13 ok
    456 20 G013 13 ok
    789 10 G013 13 check
    789 20 G013 13 check
    789 30 G120 120 check


    I would like the formula to highlight if an order number has more than one Plant per (line) Item, if so state 'check' otherwise 'ok'.


    My current formulae are as follows:

    h helper column: =IF(C2="G013",13,IF(C2="G120",120,"error"))...i did try =RIGHT(C2,3) but that didn't work.
    f formula column: =IF(AVERAGEIF(A:A,A2,D:D)=D2,"ok","check")


    Many thanks,
    Rich

  2. #2
    Board Regular
    Join Date
    Apr 2018
    Location
    UK
    Posts
    224
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: average of non-numeric cells without helper column

    When using RIGHT OR LEFT or something of that nature it will always convert it into text. To get around this you can use =RIGHT(C2, 3)+0 to convert it to a number.
    VBA is ten percent luck
    Twenty percent skill
    Fifteen percent concentrated power of will
    Five percent pleasure
    Fifty percent pain
    And a hundred percent reason to remember the name

  3. #3
    Board Regular
    Join Date
    Oct 2009
    Location
    UK
    Posts
    324
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: average of non-numeric cells without helper column

    Aha! I remember that one....thanks for that, this makes the helper column formula much easier to control but the is there a way to lose the helper column completely?

    Can a formula recognise non-identical text values...I guess that using an AVERAGE formula is causing the issue as this must relate to numerical figures.


    Many thanks,
    Rich

  4. #4
    Board Regular
    Join Date
    Apr 2018
    Location
    UK
    Posts
    224
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: average of non-numeric cells without helper column

    Have to be honest, im not 100% sure on what you are after but maybe this:

    =IF(COUNTIF(A:A, A2) = COUNTIFS(A:A, A2, C:C, C2), "OK", "CHECK")

    this also implies that G does not matter since it looks like it appears at the front of all your plants.
    Last edited by Finalfight40; Aug 22nd, 2019 at 06:03 AM.
    VBA is ten percent luck
    Twenty percent skill
    Fifteen percent concentrated power of will
    Five percent pleasure
    Fifty percent pain
    And a hundred percent reason to remember the name

  5. #5
    Board Regular
    Join Date
    Apr 2018
    Location
    UK
    Posts
    224
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: average of non-numeric cells without helper column

    Quote Originally Posted by Finalfight40 View Post
    Have to be honest, im not 100% sure on what you are after but maybe this:

    =IF(COUNTIF(A:A, A2) = COUNTIFS(A:A, A2, C:C, C2), "OK", "CHECK")

    this also implies that G does not matter since it looks like it appears at the front of all your plants.
    When i said G does not matter, that was the worst explanation i could give so ignore that part.
    VBA is ten percent luck
    Twenty percent skill
    Fifteen percent concentrated power of will
    Five percent pleasure
    Fifty percent pain
    And a hundred percent reason to remember the name

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,203
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: average of non-numeric cells without helper column

    Maybe
    =IF(COUNTIFS($A$2:$A$8,A2,$C$2:$C$8,"<>"&C2)>0,"check","ok")
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  7. #7
    Board Regular
    Join Date
    Oct 2009
    Location
    UK
    Posts
    324
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: average of non-numeric cells without helper column

    Thanks a lot, both solutions work and no need for the helper column

  8. #8
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,203
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: average of non-numeric cells without helper column

    Glad we could help & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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