• 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

  • Re: Countif & min


    Try this, assuming that you need to copy the formula down from D8 to D15 (or further down if need be). This formula will work for whatever Benefit Code (1 to 9) is entered in F8 to F15.


    =IF(NOT(AND(B8>0,$C$19>0)),0,IF(OR(B8>0,$C$19>0),IF(B8>0,IF($C$19>0,MIN(B8*C8*VLOOKUP(F8,$H$8:$I$16,2,0),$C$19*VLOOKUP(F8,$H$8:$I$16,2,0),$G$2,$G$3,$G$5),MIN(B8*C8*VLOOKUP(F8,$H$8:$I$16,2,0),$G$2,$G$3,$G$5)),MIN($C$19*VLOOKUP(F8,$H$8:$I$16,2,0),$G$2,$G$3,$G$5)),0))

    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.

  • 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

  • Re: Countif & min


    I think I understand what you mean!


    Try this in D8 copied down to D15


    =IFERROR(IF(AND(B8>0,$C$19>0),MIN(B8*C8*VLOOKUP(F8,$H$8:$I$16,2,0),$C$19*VLOOKUP(F8,$H$8:$I$16,2,0),$G$2,$G$3,$G$5),IF($C$19>0,MIN($C$19*VLOOKUP(F8,$H$8:$I$16,2,0),$G$2,$G$3,$G$5),IF(B8>0,MIN(B8*C8*VLOOKUP(F8,$H$8:$I$16,2,0),$G$2,$G$3,$G$5),0))),0)


    and this in G8 copied down to G15


    =IF(OR(D8>0,$C$19>0, $G$2>0, $G$3>0,$G$5>0), MIN( $D8,$C$19, $G$2), 0)

    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.

  • 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


    Are benefit codes 4 and 6 the only ones that need an additional formula to check if the value exceeds a minimum? What about codes 5, 7, 8 and 9?


    You say that E8:E15 must be taken into account, but all those cells are empty in your sample file, what goes in those cells, and how does the value of each cell need to be incorporated into the formula?


    Cell G12 has a value of 0 unless B12, C12 and F12 (and now probably E12 too) are not empty. In your sample file G3, G4 and G5 are all empty!


    It may be best if you attach another file that has a complete set of data, manually enter what the results of the new formulas should be and full explain the logic that you used to calculate those manually entered values.

    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.

  • Re: Countif & min


    Hello,


    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

Participate now!

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