[Solved] Deductions and Refund Formula

M

Maps

New member
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.
 

Attachments

Maps said:
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.
Click to expand...
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)))
Click to expand...

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

Regards,
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.
 
Maps said:
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.
Click to expand...
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)))
Click to expand...

OUTPUT:

MAPS.png​

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

Regards
Lutfor Rahman Shimanto
 

Attachments

Maps said:
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?
Click to expand...
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))),"")
Click to expand...

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

Regards
Lutfor Rahman Shimanto
 

Attachments

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.
 
Maps said:
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.
Click to expand...
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))))
Click to expand...

Regards
Lutfor Rahman Shimanto
 

Attachments

Maps said:
Thank you once again. I'm giving you 100% thumps up.
Click to expand...
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
 
Maps said:
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.
Click to expand...
Good afternoon.
I was helped with this formula and it works perfectly.
However, when I use the formula monthly as per Column A the deductions apply correctly provided there are zero deductions in the previous months and as long as there are values on the balance.
When the balance reaches zero what follows is #VALUE!. Even when some employees have zero deductions it yields the same result and when the employees subsequently have something to deduct it does not apply correctly because of the #VALUE! as can be seen on the attached file.
Again if I copy the formula from the period of Mar 2025 to the previous months, the formula does not give the desired result.
Can somebody help me.
 

Attachments

Hello Maps,

You can use the following formula.

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

Explanation:
  • IFERROR(..., ""): Prevents #VALUE! if lookup fails.
  • VLOOKUP(..., TRUE): Allows for approximate match, which is better for bracketed rates.
  • Returns a blank ("") if something’s wrong instead of crashing.
Update the Sheet:
  • Replace the old formula with this one in cells like G33.
  • Drag it up and down, should no longer show #VALUE!, even if there's zero balance.
  • Check that deductions or refunds start working again once there's a value in A33.
 
Thanks Shamimarita for your kind response. However, I would request that we practically use the spreadsheet with your suggested solution. When I try and insert the formular, it throws me into a wilderness.
Also, I would like the deduction result to be linked to the months listed on column A.
 
Hi Maps,

Thank you for your detailed follow-up.

I understand it can feel overwhelming when the formula doesn't behave as expected, especially when you're applying it monthly and trying to track deductions linked to values in Column A.

Updated Formula:

Try this formula in your deduction column (for example, Column G):
=IF(OR(A33="",A33=0), "",
IF(A33=-100,
0,
IFERROR(
VLOOKUP(A33, $A$3:$C$11, 3, TRUE) +
(A33 - VLOOKUP(A33, $A$3:$C$11, 3, TRUE)) *
VLOOKUP(A33, $A$3:$D$20, 4, TRUE),
""
)
)
)
Why This Works:
  • The IFERROR function prevents the #VALUE! error from breaking your sheet.
  • Using an approximate match (TRUE) makes VLOOKUP more flexible to find the closest match in your ranges.
  • Instead of showing an error, it returns a blank when no data is available.
  • You can drag or copy it across months without needing manual adjustments.
Practical Steps to Use:

  • Paste the formula starting in the appropriate deduction cell (for example, G33).
  • Make sure the lookup tables ($A$3:$C$11 and $A$3:$D$20) are correctly set up and contain the expected values and thresholds.
  • Drag the formula up or down as needed. It will adjust automatically based on the value in Column A.
If the result is still blank, double-check that the value in Column A is numeric and falls within the expected range of the lookup tables.
 
Thanks Shamima for your response.
I have tried the formula on column G33 it does not give a meaningful answer.
I have attached the spreadsheet for your scrutiny.
 

Attachments

You must log in or register to reply here.

Online statistics

Members online
0
Guests online
7
Total visitors
7
Totals may include hidden visitors.

Forum statistics

Threads
403
Messages
1,783
Members
840
Latest member
Minus2003

Share this page

Back
Top