[Solved] Deductions and Refund Formula

[Maps]

Good day.
Good morning.
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.

 

[Lutfor Rahman Shimanto]

Good day.
Good morning.
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.

Hello Maps,

Thanks for reaching out on our platform. After exploring the attached Workbook, I understand that you want the table to determine how much refunds should be affected per period. The refunds are not done at once but should be spread over periods. For your demonstration, you may use the below formula to calculate the Refunds.

=MAX(0,IF(Excess>=0,Excess*Rate on excess,(Fixed Deduction per Table-Expected Deduction)))

Feel free to contact us again with any other inquiries or concerns.

Regards,
Lutfor Rahman Shimanto

 

[Maps]

Thank you so much. Can you please help me by applying the formula on my spreadsheet to see if it gives the required results on G33 to G35 which has to be the same result on F33 to F35.

 

[Lutfor Rahman Shimanto]

Thank you so much. Can you please help me by applying the formula on my spreadsheet to see if it gives the required results on G33 to G35 which has to be the same result on F33 to F35.

Dear Maps,

Thanks a ton for explaining your issue with such clarity. You asked me to apply the formula on my spreadsheet to see if it gives the required results on G33 to G35, which has to be the same result on F33 to F35. As you requested, I have implemented a complex formula using the IF, VLOOKUP function. I am attaching the Workbook used to investigate the problem you introduced.

=IF(A34=-100,(A34-0)*100%+0,VLOOKUP(A34,$A$3:$C$11,3)+((A34-VLOOKUP(A34,$A$3:$C$11,3))*VLOOKUP(A34,$A$3:$D$20,4)))

OUTPUT:

​

Please do not hesitate to contact us with any additional questions or problems regarding Excel.

Regards
Lutfor Rahman Shimanto

 

[Maps]

Thank you so much. It's working perfectly.

 

[Maps]

The last question that I have is if cell A is blank or has a Zero, how do you make the result Blank instead of a Zero?

 

[Lutfor Rahman Shimanto]

Thank you so much. It's working perfectly.

Great to hear that the solution worked perfectly and you understand the mechanism of the formulas, MAPS! Currently, I am working on your new query, and I hope to come up with a prominent way. Good luck.

Regards
Lutfor Rahman Shimanto

 

[Lutfor Rahman Shimanto]

The last question that I have is if cell A is blank or has a Zero, how do you make the result Blank instead of a Zero?

Hello MAPS,

Thank you for your query. You can use the mentioned formula given to overcome the issue. Additionally, I use another two functions, IF and OR functions, to be more specific, and the rest of the formula is exactly as it was.

=IF(OR(A33<>"",A33<>0),IF(A33=-100,(A33-0)*100%+0,VLOOKUP(A33,$A$3:$C$11,3)+((A33-VLOOKUP(A33,$A$3:$C$11,3))*VLOOKUP(A33,$A$3:$D$20,4))),"")

Likewise, I am attaching the Workbook to help you understand better.

Regards
Lutfor Rahman Shimanto

 

[Maps]

Thanks once again. It seems when you delete the value in A33 or put a zero the result on G33 gives a ZERO instead of a BLANK. Can you please recheck for me.

 

[Lutfor Rahman Shimanto]

Thanks once again. It seems when you delete the value in A33 or put a zero the result on G33 gives a ZERO instead of a BLANK. Can you please recheck for me.

You are right, Maps! If we delete the value in A33 or put a zero, the result on G33 gives a ZERO instead of a BLANK. I am using the Equal sign (=) instead of the Not Equal (<>) sign this time. It is working perfectly on our end. Hopefully, it will resolve your issue.

=IF(OR(A33="",A33=0),"",IF(A33=-100,(A33-0)*100%+0,VLOOKUP(A33,$A$3:$C$11,3)+((A33-VLOOKUP(A33,$A$3:$C$11,3))*VLOOKUP(A33,$A$3:$D$20,4))))

Regards
Lutfor Rahman Shimanto

 

[Maps]

Thank you once again. I'm giving you 100% thumps up.

 

[Lutfor Rahman Shimanto]

Thank you once again. I'm giving you 100% thumps up.

Dear Maps

I am writing to express my sincere gratitude for your kind words and support. It means a lot to us to receive such positive feedback from you. Please know that it was my pleasure to assist you. Thank you once again for your trust in us, and please do not hesitate to reach out if you need further assistance.

Best Regards,
Lutfor Rahman Shimanto
ExcelDemy