Hello everyone.
Is there anyone who can help me with this request.
Someone helped me with this formula =IF(A20=0,"",LOOKUP(A20-0.01,$A$4:$A$13,$D$4:$D$13)*(A20-LOOKUP(A20-0.01,$A$4:$A$13,$C$4:$C$13))+MIN(A20,LOOKUP(A20-0.01,$A$4:$A$13,$C$4:$C$13))) to create a deducton table for shortages as per the attached file and it works perfectly.
I however, wish to add on the table justifiable refunds highlighted in RED. In the event someone has an over in his cash ups, I want the table to determine how much refund should be effected per period as the refunds are not to be done at once but should be spread over periods.
Is there anyone who can help me with this request.
Someone helped me with this formula =IF(A20=0,"",LOOKUP(A20-0.01,$A$4:$A$13,$D$4:$D$13)*(A20-LOOKUP(A20-0.01,$A$4:$A$13,$C$4:$C$13))+MIN(A20,LOOKUP(A20-0.01,$A$4:$A$13,$C$4:$C$13))) to create a deducton table for shortages as per the attached file and it works perfectly.
I however, wish to add on the table justifiable refunds highlighted in RED. In the event someone has an over in his cash ups, I want the table to determine how much refund should be effected per period as the refunds are not to be done at once but should be spread over periods.