Formula Question?

    Hello,


    I have a question regarding a formula that I believe is possible by some one with more experience with formula's then myself. I have about 3 days worth of experience working with excel formula's.


    Here is the scenario:
    I have a spread sheet with a formula for a MAX in a cell at the top of a column. This cell is at the top of the page in a section that is a locked window pane. This Cell is I7. The data in which I am getting the max from is row 12 to row 70 in the same column but they have a formula in every other cell.


    Formula in every other cell in I12:I70 is:
    =IF(AND(I12="",I13=""),"",(I12+I13)/2)


    Due to this formula it places a 100% if there isn't any data in the previous cells in the row.


    So what I have is I12 through I70 with a blank cell and then a 100%


    Now back to Cell I7 and the max formula. I need a formula that has to logical conditions.
    Logical 1: IF the number is >0% use it
    Logical 2: IF the number is <100% use it


    I tried something like this but ended up with an error
    =MAX(IF(AND(I12:I70<100%,I12:I70>0%),I12:I70,""""))


    Sorry if this is too in depth or not enough info. I will gladly provide what is needed to get help.

    Welcome to OzGrid


    You are very close. The following, entered as an array formula (SHIFT+CTRL+ENTER) will give you what you are after (if I've understood correctly).


    =MAX((I12:I70>0)*(I12:I70<1)*I12:I70)


    If entered correctly it will show in eth formula window with {} around it.


    It works by creating arrays of TRUEs (=1) and FALSEs (=0) based on the inequalities adn then multiplies the resulting array of 1's (meets both criteria) and zeros times the column I array of numbers. It then selects the MAX of the resulting array.


    NOTE: this does not work for MIN, since the zeros will be the minimum values.


    See the attached example file. The values in I12:I70 are variables, so each time you hit F9 on make a change, they will change. CONDITIONAL FORMATTING is used to show which cell has the max value shown in I7.

    Thank you Thomach,


    That works lke a champ.


    =MAX((I12:I70>0)*(I12:I70<1)*I12:I70)


    Would I be able to apply this formula you provided to a STDEVP and an AVERAGE with the same conditions?


    Once again Thanks alot for the help.

    My assumption is that it will NOT (repeat not) work with those two functions, because they would be evaluating the array that includes the zeros (FALSE) that you want to exclude from the calculations. That is, they will return an answer, but it will not be accurate for what you want.


    For the StdDev you will probably need to create a new column that selects only the non-zero and non-100% values and then work against these values.


    You should be able to use SUMPRODUCT with the logic statements only to "COUNT" the number of values that meet criteria, and then also with the multiplication of the column to get the total of values that are to be included. Divide the second by the first and you will have an appropriate average. That is:


    =SUMPRODUCT((I12:I70>0)*(I12:I70<1)*I12:I70))/SUMPRODUCT((I12:I70>0)*(I12:I70<1))

    ctrolz & thomach, where would this formula be applied in a real world scenario, I'd like to understand the formula but without the why, I'm a little lost.

    Sorry for the delayed response.


    I can only tell you my real world scenario. I work in a Manufactuering plant where we have a Qaulity Control Department. They are using a spread sheet to record the various tests they perform on the product through out the day. It would probably be ideal to change this to an access DB or something similar as I am starting to believe there is just too much data to put on a spread sheet.

Participate now!

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

Register Yourself Login