Formula to return the relevant multiplier value for fines

[The_Snook]

Morning all,

I have a schedule of fines and in some instances a multiplier effect is activated based on two criteria (no of consecutive failures & no of failures in a rolling 12 month period). I'd like a formula that returns the relevant multiplier effect (which is always the higher value) if either of these criteria is applicable.

Example attached.

Thanks in advance,

Snook

[samba_ravi]

H5=IF(H4=0,0,SUMPRODUCT(MAX(ISNUMBER(1/(COUNTIF(OFFSET(H4,,{0,-1,-2,-3,-11},,{1,2,3,4,12}),">0")>={1,2,3,4,6}))*{1,1.5,1.75,2,2}*({1,2,3,4,12}<=COLUMNS($H5:H5)))))

Please Login or Register  to view this content.

Try this and copy towards right

[The_Snook]

Hi nflsales,

Thanks for this.

There seems to be a discrepancy in month 6 as it is returning a multiplier of 1 when I would have expected it to be 1.75 as it is the 4th failure in a 12 month period.

Regards,

Snook

[samba_ravi]

I misunderstood your requirement try below formula, Try below formula and copy towards right
H5

Please Login or Register  to view this content.

[The_Snook]

I have no idea how it works but it works a treat. Thank you!

Snook

[The_Snook]

Hi nflsales,

Is it possible to tweak the formula that you kindly provided for me yesterday to calculate the total fines due rather than the multiplier effect?

Updated spreadsheet attached.

Thanks,

Snook

[samba_ravi]

Will you show me the expected result?

[The_Snook]

Hi nflsales,

I've updated the attached with further detail, hopefully this makes sense?

Thanks,

Snook

[samba_ravi]

The below formula is an array formula please confirm it with Ctrl+Shift+Enter
H27

Please Login or Register  to view this content.

Try this and copy towards right, hope some one will come up with simple formula

[The_Snook]

Unbelievable solution!

Massive thanks for this!

Snook