Posts by Aussiebuilder

    Hello there,

    I have attached a sheet which contains benefits with one or two criteria

    Although the formula in range E8:E16 does work, I feel the formula C8:C16 maybe better (able to be shortened (instead of having a separate formula for all the criteria 1-9 within each cell of the range C8:C16) for each number (between 1-9) with index/match (as there is multiple criteria) involved

    Currently the numbers (1-9) are allocated to their own separate cells

    Thank you for the assistance


    I have attached a sheet which gives a benefit based on a benefit code from 1-9

    The formula I created in range C8:C16 was created to validate based on quote in column A & ADA schedule in column D

    Although this links to column H, I wonder if VLOOKUP formula could incorporate the benefit codes instead? Not sure how to setup

    Column F is designed to give the lowest of the quote, ADA schedule or limit (range F2:F5)

    I have spent hours trying to figure out the best formula for calculating the balance carried forward in F8:F16, I need the result to be either the maximum (highest) result if more than column C or D from range F2:F5

    I have a formula which does sort of work but is incomplete

    Thank you

    Re: Countif & min


    I have attached a new sheet

    The benefits examples have all been provided from 1-9 although the actual benefits are for illustration purposes only, in actual fact if the limits remaining (G2, G3, G5) are depleted would be zero, that's the part I can't figure out as would create circular references

    The VLOOKUP formula can be updated to include the new criteria

    The only thing left is how to amend the benefits range in two ways

    Benefits paid either (minimum) C8:C16 (individual row), or balance of limit available either G2, G3 or G5 (this formula needs amending to show balance remaining from associated cells F2, F3 & F5 based on benefits paid C8:C16

    I have now incorporated D8:D16 if a value exists into C8:C16 (which may/may not be best formula)

    This calculation is close to creating circular references so is tricky, again not sure how to incorporate into range F8:F16

    Thank you

    Re: Countif & min

    Hello & thank you for the amazing formula

    Just a couple of things

    The range I8:I16 that you have created the VLOOKUP section is more than just a percentage for example 4 is 70% of quote or $350.00 each (whichever is lower) 6 is 75% of quote or $98.00 each (whichever is lower) the formula needs to include E8:E15 in the formula for range D8:D15

    Lastly see cell G12, it is allowed to be more than the available limit in G3, I. E limit available $300.00 & total benefits in range G8:G12 of $330.00, how to stop benefits being more than available limits?

    Re: Countif & min

    Thank you

    However the formula seems to only validate if there is an amount above zero in C19 only, does not seem to work if C19 is blank & D8 contains a value above zero

    Also range G8:G15 seems to have been missed based on minimum of d8*c8*I8, c19*I8, G2, G3 or G5

    The limits remaining reduce in the G2:G5 range (as the benefits range G8:G15 are calculated) so how do I incorporate so benefits paid (G8:G15) = lowest limit remaining if less than the original min calculated in G8 carried down the column

    Thank you

    Hello Forum users,

    I have attached a sheet with MIN B8 & C19.

    However the formula in D8 needs to calculate the minimum of either C19 (as per formula) or B8 (as per formula), whichever is not blank or zero, however does only seem to work on C19 only not D8 (what I am I doing wrong) with countif?

    Also, G8 provides the result of the minimum of the cells/criteria, currently has a result of $300.00 & the balance H2 is the result of G2-G8
    How do I have a carried forward reducing balance on cell range G9-G15 based on the results of D9-D15

    Thank you