Hello.



I have a formula currently as follows

Where G2 is the limit,C13 is one quote & D3 is another quote, E4 is a the final criteria.

The rows are G5:G10 (where the result populates)

Also range A1:A15 which is just quantity as a numeral

Also have I1:I15 which consists of different criteria to multiply the base quote against

Another cell range F is where I place a number from 1-15 to pull the data from range i1-i15

=IF(AND($F1=1,(ISNOTBLANK($E4), MIN($G$2,$C$13*I1,$D3*I1,$E4*A1,0,))

Is this formula right or the best formula to use? Want it to be validated only if the cells in the min formula are more than zero or not blank.

This result appears in the first row in the range (G5)

If the result means the limit (G2) is depleted, thus means any further results in the range must equal zero also,(G6-G10),how do I incorporate this into the formula?

The limit (G2) is/was $1,200.00 & now validated in (G5) as zero

I think it would be much easier to help if you uploaded a sheet with your data and gave examples of wanted results.

For my part I am a bit confused after having read your message.

=NOT(ISBLANK( . . ))

=if(not(ISBLANK(C13)),if(not(ISBLANK(D8)), if(F8=1, MIN(G2, D8*I15, C13*I15, E8*A1, result in cell G9, $400.00 (as G2 is the lowest amount, D8 is zero/blank & C13 is zero/blank)

If the formula is then run again in the next row G10, how do I add to the formula so that it validates that G2 is now zero & no further limits/benefits are available

C13 is a set amount (a lump sum instead of individual amounts like D8) multiplied by I15 50% (benefits max 50% of this total)

D8 is a set quoted amount multiplied by a quantity A1*I15 being 50% (benefits max 50% of this total)

G2 is a limit remaining & the total available benefits

So the formula I created needs to ignore D8 or C13 is the cells are blank/zero & give the minimum of the remaining criteria being the minimum result as the benefits payable

The column I in the range I15:I20 contains several different validations such as by 50%,60%,80% 100%, 80% or item amount (whichever is lower) of quote again either C13 or D8 whichever is not blank/zero

Could I vlookup formula be incorporated maybe?

Thank you