Total Discount value based on multiple criteria

Dear All forum contributors,

My current problem is that my boss has given me the ledger in which there is the column of Date,Sales Bill,Sales Amount and Payment Received respectively in column B,C,D,E.He has also given me the condition/ criteria saying that you have to calculate the discount amount to be given in each bill if the condition fulfills which is in next sheet.Let me explain the terms & condition-

If the payment received within 15 days from Bill date then give discount of 15% of sales amount
If the payment received within 30 days from Bill date then give discount of 10% of sales amount
If the payment received more than 30 days from Bill date then give discount of 0% of sales amount

How to solve this problem in order to arrive total discount amount in solution sheet.

If you require any further info.pls do let me know.

I will be eagerly waiting for all top contributors and others for help.Below is attached excel workbook.

With Best Rgds,
Suresh

Originally Posted by RobertMika

If yuo will receive payment for NO.1 let say, where this will be added?
Under the first row(you will add new row)?
Does partial payments count or you need only when whole payment is recived o time?

Dear Sir,

Thanks for your reply.The payment shall be partial or whole but while calculating discount total value under each bill must match which might take allocation,how you do it depends upon how analyse and work upon it.I have posted same post with in detail with worksheet attached in post at :

http://www.excelforum.com/excel-form...ived-date.html

Slightly more descriptive I have attached excel sheet with manual workings at Right Hand Side of 1st worksheet is presented.Kindly undergo thru it.You can use any method to get the total discount amount based on condition in 2nd worksheet.

If you require any further info.kindly do let me know.

Also,I have attached another revised workbook for your kind reference which you will find in above link too.

With Best Rgds,
Suresh