Capped money based on groups and months.

Hi there,

I have grouped the date into bands 0,1,2.

If it band 0, then it is just equal to the paid_incurred.
If it in band 1 or 2 in each dev_month, the paid incurred is capped by 150, then the rest of the money goes to the next column which is paid by someone else. I tried using sumifs and ifs functions in conjunction to produce it based on the band and dev_month, but I am a slight problem with it.

On the sheet, you can also see the expected result I did which was very tedious.

Thanks

I think I have probably writing a bad example in my sheet, because I deleted some parts off which mad the dev_mth inconsistent with the dates, ignore that for now.

Band 1 is the order been purchased between 03/12/2016-07/12/2016. and Band 2 is order been order between 25/12/16 - 29/12/16.

Each dev_month within each band has a capped of £150 excluding band 0.

So if an order take place on 03/12 (2nd row) is band 1 and has dev_month 1 for £50. There is another order at row 18, band 1 and dev_month 1 for £130. The cumulative amount is £180 for that band 1 and dev_mth 1. As you can see on row 18 on the xol_incurred is £30, because my payment is capped at 150 during that month and dev_month.

Does this help?

In H2

=IF($E2=0,$C2,IF(SUMIFS($C$2:$C2,$D$2:D2,$D2,$E$2:$E2,E2)>150,MAX(150-(SUMIFS($C$2:$C2,$D$2:$D2,D2,$E$2:$E2,$E2)-$C2),0),$C2))

In I2

=$C2-$H2

Custom format cells as 0;;;@

Originally Posted by JohnTopley

In H2

=IF($E2=0,$C2,IF(SUMIFS($C$2:$C2,$D$2:D2,$D2,$E$2:$E2,E2)>150,MAX(150-(SUMIFS($C$2:$C2,$D$2:$D2,D2,$E$2:$E2,$E2)-$C2),0),$C2))

In I2

=$C2-$H2

Custom format cells as 0;;;@

Thank you!!!! =)

Originally Posted by rahi_feri

Hi
Ok it looks it is solved(Mr. Topley' answer)! i did it in opposite way and i think it is wrong!
this time i'm wanna ask question!
in one of the row that i've highlighted there is a difference do you know why !?!

I think the extra 10 is coming from row 21?

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.