Find the max value if the value is not struck through

    This should be a relatively simple formula, but I am finding it quite difficult. I am trying to find the max value out of a set of three values, but I want to not include one of them if it is struck through. I am using conditional formatting to do the strike through. Is this possible?

    Re: Find the max value if the value is not struck through


    We would probably need to know the formula or the logic used in determining which values you are striking through.


    For example if I have conditional formatting that will change the font of numbers over 100 to bold, then without code I can't determine if these numbers are bold.


    I can use the greater than 100 in a formula to exclude those numbers making it appear that I have determined those numbers have a bold font.


    I want the max number if the font is not bold.


    Array formula for that would look like this.

    Code
    =MAX(IF(A1:A10<100,A1:A10,""))


    EDIT: Even if we were to use code we wouldn't be able to build a custom function. Display format, which is what you could use in a sub procedure to check the conditional format formatting does not work in functions.

    Bruce :cool:

    Re: Find the max value if the value is not struck through


    What I have is something like this:
    [TABLE="class: grid, width: 500, align: left"]

    [tr]


    [td]

    A1

    [/td]


    [td]

    B1

    [/td]


    [td]

    C1

    [/td]


    [td]

    D1

    [/td]


    [td]

    E1

    [/td]


    [td]

    F1

    [/td]


    [td]

    G1

    [/td]


    [/tr]


    [tr]


    [td]

    60

    [/td]


    [td][/td]


    [td]

    63

    [/td]


    [td][/td]


    [td]

    67

    [/td]


    [td]

    x

    [/td]


    [td]

    63

    [/td]


    [/tr]


    [/TABLE]







    G1 would be the cell where the MAX value is found. The range is A1, C1, E1. Using conditional formatting =IF(B1="x",TRUE,FALSE).
    I am open to other methods and the structure is not set in stone, although I would prefer horizontal because there is other information.

    Re: Find the max value if the value is not struck through


    I'm not a real formula whiz, but with your data I managed to throw something together that works.
    In G1 with your data.

    Code
    =MAX((((B1:F1)<>"x"))*(IF(ISNUMBER((A1:E1)),A1:E1,0)))


    After copying the formula into the cell you must use Control + Shift + Enter to confirm the formula, not the usual Enter.

    Bruce :cool:

    Re: Find the max value if the value is not struck through


    Quote from skywriter;767713

    I'm not a real formula whiz, but with your data I managed to throw something together that works.
    In G1 with your data.

    Code
    =MAX((((B1:F1)<>"x"))*(IF(ISNUMBER((A1:E1)),A1:E1,0)))


    After copying the formula into the cell you must use Control + Shift + Enter to confirm the formula, not the usual Enter.


    Thanks. That works.

Participate now!

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

Register Yourself Login