Hello there,
I have attached a sheet which gives a balance or benefit based on multiple conditions
The range is F8:F16.
It does contain multiple if statements as several variables are involved
Is there a better way to create this result as although these formulas work they are very long winded?
Thank you
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
Hello.
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
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
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
Re: Countif & min
Adding attached file
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
