Formula find max value for multiple if thens.. between: ">" or "<" and a ","

    hi, i am trying to get a number value from within a formula. there are multiple copies of the same expressions within the formula (eg below), where, if possible: max for all values (between: ">" or "<" and: "," thanks in advance.
    exampleS of the formula using is of varying lengths, eg anywhere from 3 to 10 or more expressions.


    max of all would come up, if i were using a "less than" value sign: "<" thereby putting the max to the right of the examples. one single max output is all that is needed.
    all numbers on first 2 lines are: 4 2 1.15 0.7 the 2nd & 3rd examples show embedded if thens if that matters.



    =IF(A9>4,"X",IF(A9>2,"Z",IF(OR(A9>1.15,1),"Y",IF(OR(A9>0.7,0),"B","A"))))
    =IF(A9>2,IF(A9>4,"X","Z"),IF(OR(A9>1.15,1),"Y",IF(OR(A9>0.7,0),"B","A")))
    .
    =IF(OR(A9>0.02,0*(B9=1)),IF(A9>5,"X",IF(A9>0.8,"ZAX",IF(A9>0.3,"XYZ",IF(A9>0.2,"ABX",IF(A9>0.1,"CBE","FAX"))))),
    IF(OR(A9>0.01,0*(B9=1)),"XYZ",IF(OR(A9>0.005,0*(B9=1)),"XYZ",IF(OR(A9>0.001,1*(B9=1)),"X",IF(OR(A9>0.0005,1*(B9=1)),"ABC","AFN")))))




    SUMPRODUCT(MAX ??
    i have dozens of examples looking at but not sure any for formulas, most come up errors & am not sure where to start, bunch more than this but not clicking yet/ right now again how these work. (i added the left 13 on all of them)


    =LEFT(FIND(MID(AO1552,(FIND(">",AO1552)+3),10),AO1552),13)
    =LEFT(TRIM(LEFT(AO1562,FIND(">",AO1562)-1)),13)
    =LEFT(--TRIM(LEFT(AO1562,FIND(">",AO1562)-1)),13)
    =LEFT(MID(LEFT(AO1562,FIND(">",AO1562)-1),FIND(",",AO1562)+1,LEN(AO1562)),13)
    =LEFT(MID(AO1562, FIND(">",AO1562)+1, FIND(",", AO1562, FIND(">", AO1562)+1)-FIND(">",AO1562)-1),13)
    =LEFT(MID(AO1562,FIND(">",AO1562)+1,FIND(",",AO1562)-FIND(">",AO1562)-1),13)
    =SEARCH(",",AO1562)-SEARCH(">",AO1562)-1

    Re: Formula find max value for multiple if thens.. between: &quot;&gt;&quot; or &quot;&lt;&quot; and a &quot;,&quot;


    Update: i usually am trying to find the answer at the same time, and forget to put what i have.. or at least a word problem version of what looking for.
    wild guesse - word version only: (is the following too funny, or does it help) :)


    =sumproduct(find(max(mid(OR({">","<"}), [number looking for], mid(OR({">","<"})), mid(","))))


    --------


    (strings? if i might have used in a general term.. how to say what looking for? besides the number values between a couple of characters, eg's: > and comma, or: < and comma )
    hi, thanks. i didn't realize that wouldn't be clear.. (did leave the list of numbers that would be the result of what looking for, but still would be hard to see.. pardon).
    for the first line, the first number trying to pluck out is: 4
    =IF(A9>4,"X",


    between the > sign & comma, is a 4 for the set of numbers to get are: 4 2 1.15 0.7
    (sometimes the formulas are set up with < signs, in place of the greater than signs.. get it from there / see what i am doing?


    the 2nd line has a changed order due to if then embedding so tested items order changes to: 2 4 1.15 0.7 so the tested order changes (hence need to test all items for max, looking for: 4).
    and if it matters: when use the less than sign the whole order is changed (numbers searching for are between < signs & a comma: for the numbers: 0.7 1.15 2 4
    answer looking for (numbers will change but same here), looking for the max number: 4


    =IF(OR(A9<0.7,0),"B",IF(OR(A9<1.15,1),"Y",IF(A9<2,"Z",IF(A9<4,"X","ZZ"))))



    i wonder that the answer would be a sumproduct(max kind of thing. thanks.

    Re: Formula find max value for multiple if thens.. between: &quot;&gt;&quot; or &quot;&lt;&quot; and a &quot;,&quot;


    Forgive me, but I have absolutely no idea what you are asking for.


    You appear to have given almost exclusively a series of excerpts from your own formulas. Yet I still cannot see a single example with expected result.


    Regards

    Re: Formula find max value for multiple if thens.. between: &quot;&gt;&quot; or &quot;&lt;&quot; and a &quot;,&quot;


    hi, thanks for the reply. i would often get either put not enough info, or too much, pardon. for getting replies, sorry to say.. from some that just take advantage of that.. etc..


    a shorter example, hope i convey well-enough.
    for a problem in other columns in sheet the factor of values can increase any number, say: 10x, where the measure values in this example become obsolete.


    anywhere i have a value in the formula for eg FIND / get the 7 >>> IF(A9>7,"x", <<< (I Need to pull out the max of all the numbers, which are between the ">" sign and the comma: ",")
    the max number can be anywhere in the formula in between a ">" or "<" and a comma: ",": not just the first or last expression.

    NOTE: SOME FORMULA's use greater than signs ">", and some use less than signs "<". a solution needs to find either.


    for below: i need to get max of: MAX(7, 9, 3.5, 2, .50), which is: 9, for a bunch of IF's in the same formula. << WHAT NEED: get the max of these example numbers, from within the formula below.


    IF(A1>7,IF(A1>9,"Y","X"),IF(A1>3.5,"A",IF(A1>2,"B",IF(A1>.50,"C","D")))) << the max here for all numbers between all ">" & "," EXPECTED MAX RESULT IS: 9 (i will use the 9 for elsewhere, if supposed to be now 90, then i have a problem).


    a wild guesse on my part, call it a word problem example (as above post):


    =sumproduct(find(max(mid(OR({">","<"}), [number looking for], mid(OR({">","<"})), mid(","))))



    i can break it down into smaller pieces, but is same as above:


    for: IF(A9>7,"x",
    i need to get the number: 7


    for a formula that has 1 to maybe 12 IF's for any result: 7, i need to get the max of those numbers.

    Re: Formula find max value for multiple if thens.. between: &quot;&gt;&quot; or &quot;&lt;&quot; and a &quot;,&quot;


    I only asked for a few examples with expected results...


    I'm afraid I'll have to pass on this one now. Hopefully someone else will pick up on this thread shortly.


    Regards

    Re: Formula find max value for multiple if thens.. between: &quot;&gt;&quot; or &quot;&lt;&quot; and a &quot;,&quot;


    I think what the OP is trying to do is extract all the numbers on the right side of any > or < from within the formula (so would have to use Formulatext() to convert formula to string), then get the maximum of these numbers..


    so if his formula was: =IF(A1>[COLOR="#FF0000"]7[/COLOR],IF(A1>[COLOR="#FF0000"]9[/COLOR],"Y","X"),IF(A1>[COLOR="#FF0000"]3.5[/COLOR],"A",IF(A1>[COLOR="#FF0000"]2[/COLOR],"B",IF(A1>[COLOR="#FF0000"].50[/COLOR],"C","D")))) then he wants us to extract the numbers and give the max i.e. =MAX([COLOR="#FF0000"]7[/COLOR], [COLOR="#FF0000"]9[/COLOR], [COLOR="#FF0000"]3.5[/COLOR], [COLOR="#FF0000"]2[/COLOR],[COLOR="#FF0000"] .50[/COLOR]) with final result is 9.


    I looked at it for a little, but haven't come up with anything workable.... and I am done for the day here....


    Not sure if that helps you, XOR LX....

    Where there is a will there are many ways. Finding one that works for you is the challenge!

    Re: Formula find max value for multiple if thens.. between: &quot;&gt;&quot; or &quot;&lt;&quot; and a &quot;,&quot;


    Update: i have been meaning to install my new op sys & ms office. i checked the version of the command you quoted for excel 2013?
    using: xp pro 2002? / office: 2002? not see where to ref that
    on hand software: windows 7 ulitmate 32 64, mso pro 2010 32 64


    Update: just thought of / and see items for make a new function - udf to do the task, checking, re goog: excel find numbers max in a text string
    https:// encrypted.google .com /search?complete=0&num=100&hl=en&safe=off&q=excel+find+numbers+max+in+a+text+string



    ==========


    yep.. that's it. i could not think of how else to illustrate it. i am in no hurry on this.
    (i try to spend alot of time writing the question so it works; i get the same problem when trying to goog items, sometimes for hours if you don't already know the answer, you don't know what to ask).


    how to say, as above, if a factor that is common to a few areas/ columns in one line, if the base factor increases (eg main number x 10), some other columns values are also "directly related", but i do not have them connected in anyway.
    so this formula (xyz etcetera) are dates. the > 9 (or any number in formula) is trashed because a new factor might by times 10, or: 90. result: i have no indication which lines are garbage.


    ========== These are all i have right now


    still not sure if relevent, but have seen some rough format examples, with the items to be tested for as below. (ie with first test character, once on left & then again on right end along with the right most test character)
    =sumproduct(find(max(mid(OR({">","<"}), [number looking for], mid(OR({">","<"})), mid(","))))


    Best B: find text between parenthesis eg: test (9) gets the 9 out
    =MID(A9,SEARCH("(",A9)+1,SEARCH(")",A9)-SEARCH("(",A9)-1)+0


    Best A: find all between 2 words (is what referring to above, for parts left and right but i had in wrong order get wrong data type when i use > < "," then add a sumproduct to it?)
    =MID(A9,SEARCH("this",A9)+3,SEARCH("test",A9)-SEARCH("this",A9)-4)
    above to find all words beteen THIS & TEST for eg: this is a big test


    OTHER:


    checking goog: excel find number between 2 characters
    goog: https://encrypted.google.com/s…mber+between+2+characters


    not it, but all data between: < and >
    =mid(left(A2,find(">",A2)-1),find("<",A2)+1,len(A2))



    other: not get it but supposed to find data between 3rd & 4th underscore:


    =TRIM(LEFT(SUBSTITUTE(MID(A9,FIND(">",SUBSTITUTE(A9,">","|",3))+1,LEN(A9)),">",REPT(" ",LEN(A9))),LEN(A9)))

    Re: Formula find max value for multiple if thens.. between: &quot;&gt;&quot; or &quot;&lt;&quot; and a &quot;,&quot;


    hi, i am thinking a udf might work for me.. does that mean i need to post elsewhere here? i have found a couple of examples for what have so far. thanks.

    Re: Formula find max value for multiple if thens.. between: &quot;&gt;&quot; or &quot;&lt;&quot; and a &quot;,&quot;


    Here is a UDF solution



    If the formula from which you want to extract the maximum number is in cell C1 then put this in the cell where you want to display the result.


    =GetMaxNumberInFormula(C1)

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login