+ Reply to Thread
Results 1 to 8 of 8

Please need correction on SUM MID formula to find Low High on universe of 10 digits

  1. #1
    Registered User
    Join Date
    10-14-2017
    Location
    Miami, Florida
    MS-Off Ver
    2016
    Posts
    83

    Please need correction on SUM MID formula to find Low High on universe of 10 digits

    Hi,
    as the tread name say : Please can someone correct my formula? i need to display Low/High Digits on unverse of 10.

    Low Digits = 1,2,3,4,5
    High Digits = 6,7,8,9,0

    Example:
    On Column C15 (down) i have the following Digits and on K15 (down) display the output (with my formula)
    4-4-3-3-0 --> 5/0 <-correct
    6-2-0-2-8 --> 1/4 wrong - (output should be 2/3 )
    5-5-1-7-0 --> 5/0 wrong - (output should be 3/2 )
    1-9-1-4-6 --> 2/3 wrong - (output should be 3/2 )
    9-3-2-3-0 --> 3/2 <-correct
    2-9-5-0-6 --> 2/3 <-correct
    3-3-2-8-5 --> 5/0 wrong -(output should be 4/1 )
    9-7-8-6-2 --> 2/3 wrong -(output should be 1/4 )
    7-1-1-3-3 --> 3/2 wrong -(output should be 4/1 )
    1-4-8-2-3 --> 5/0 wrong -(output should be 4/1 )
    7-9-4-8-5 --> 2/3 <-correct

    On Column K15 (down) I'm using the following formula :
    Please Login or Register  to view this content.
    Please can a guru tell me what i'm doing wrong?

    Thank you !

  2. #2
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Please need correction on SUM MID formula to find Low High on universe of 10 digits

    Your array constants are wrong

    {1,1,3,9,9} should be {1,3,5,7,9}

    edit:-

    Formula might not be right, 0 will be evaluated as low, I misread part of the requirement, because of one of your 'correct' examples.

    Why is 4-4-3-3-0 5/0, shouldn't it be 4/1 if 0 is high?

    In which case, try this instead.

    =SUM(--(--MID(C15,{1,3,5,7,9},1)={1;2;3;4;5}))&"/"&SUM(--(--MID(C15,{1,3,5,7,9},1)={6;7;8;9;0}))
    Last edited by jason.b75; 07-28-2018 at 07:13 PM.

  3. #3
    Registered User
    Join Date
    10-14-2017
    Location
    Miami, Florida
    MS-Off Ver
    2016
    Posts
    83

    Re: Please need correction on SUM MID formula to find Low High on universe of 10 digits

    Hi jason.b75

    I follow as you instructed and seems that is working perfect.

    Question 1 : Is way to recognize the "zero" as high number (in replace of 10)??

    Question 2 : When, let say a library is 40 numbers, what array constant are supposed to be?

    I did it on another sheet and i place as array constants {1,4,7,10,13}
    And worked, it is a rule for it?

    Thanks, anyway i'll google it




    Thank you so much!!

  4. #4
    Registered User
    Join Date
    10-14-2017
    Location
    Miami, Florida
    MS-Off Ver
    2016
    Posts
    83

    Re: Please need correction on SUM MID formula to find Low High on universe of 10 digits

    Hi jason.b75

    Yes, you're right!!,

    Why is 4-4-3-3-0 5/0, shouldn't it be 4/1 if 0 is high? <-- My mistake, I'm sorry


    I'll try your new formula, let you know on a bit


    Thanks again

  5. #5
    Registered User
    Join Date
    10-14-2017
    Location
    Miami, Florida
    MS-Off Ver
    2016
    Posts
    83

    Re: Please need correction on SUM MID formula to find Low High on universe of 10 digits

    Hi jason.b75

    Please Login or Register  to view this content.
    didn't work, is displaying #VALUE!


    Thanks

  6. #6
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Please need correction on SUM MID formula to find Low High on universe of 10 digits

    For question 2, do you mean 40 digits per cell? It would be easier to answer with an example.

    Maybe something like

    =SUM(--MID(C15,IF(ISODD(ROW($1:$40)),ROW($1:$40)),1))

    Which would need to be array confirmed with shift ctrl enter. If 40 numbers includes any double digit numbers then a different approach would be needed.

  7. #7
    Registered User
    Join Date
    10-14-2017
    Location
    Miami, Florida
    MS-Off Ver
    2016
    Posts
    83

    Re: Please need correction on SUM MID formula to find Low High on universe of 10 digits

    for question 2.
    I used the same formula but changed the array constant to {1,4,7,10,13} and worked, i was curious about it.

  8. #8
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Please need correction on SUM MID formula to find Low High on universe of 10 digits

    Did you test the formula on the file from post #1? I entered it into K15 and it returns the correct results.

    Are you using non stantard regional settings? (decimal numbers entered as 1,5 or 1;5 instead of 1.5)?

    edit:-

    Didn't think of this initially,

    Are you using the correct array constants for the cells? You will get an error result if any of the numbers in the 1st or 3rd array constants exceeds the number of characters in the cell.

    With the example, 5 numbers + 4 dashes / hyphens = 9 characters, so any value greater than 9 passed to the start position of MID() will result in #VALUE!

    As for changing the array constant, that wil work to pick up any 5 single digit numbers from those positions with the cell. The formula suggested in post 6 was an alternative to the array constants on the assumption that there could be more than 5 numbers in the cell.

    If you only have 5 or so digits then the array constant is easy enough, if you had 100, digits, then the alternative would be easier than having to type out {1,3,5,...95,97,99}
    Last edited by jason.b75; 07-28-2018 at 08:47 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Looking for a formula to count single digits vs. double digits?
    By mnlaw in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 03-29-2023, 02:20 PM
  2. [SOLVED] separate the start and end digits and high and low digits and *
    By jorel in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 06-21-2018, 11:59 AM
  3. Greetings Excel Help Universe!
    By BaronVonTeabag in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 06-07-2018, 09:16 PM
  4. [SOLVED] find and then high lite row under find
    By tman62 in forum Excel Formulas & Functions
    Replies: 21
    Last Post: 11-23-2016, 08:08 PM
  5. [SOLVED] Lottery Filter#2, Low/High Digits, in Single CELL with "-"
    By david gonzalez in forum Excel General
    Replies: 9
    Last Post: 10-15-2013, 01:50 PM
  6. Creating an automatic updating database of the S&P universe from yahoo/google finance
    By wallstreetballa in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-21-2013, 07:45 PM
  7. Find High Score
    By Lee in forum Excel General
    Replies: 0
    Last Post: 04-16-2005, 02:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1