Formulas with Multiple Range Criteria - not returning result

[btparkhe1920]

Dear all

We have attached a file.

1) If D1 and D2 are less than 0, then the calculation is 0

2) If the voucher type is ZSOR, then the first slab is 8 days free, 2nd slab is 7 in 10% Days, 13% 16 to 60 total 45 days or remaining days pending, and 16% will
have more than 60 days

3) If the voucher type is DN, then the first slab is 15 in 10% Days, 13% 16 to 60 total 45 days or remaining days pending, and 16% will have more than 60 days

[AliGW]

Administrative Note:

Members will tailor the solutions they offer to the version of Office (Excel) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this. Thanks.

There is nothing in D1 or D2 - your instructions need to relate to the workbook you have provided, otherwise it's incredibly difficult for us to work out what's going on.

A good sample workbook has just 10-20 rows of representative data. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.

[JohnTopley]

You are not consistent with your terminology: there is no Voucher type as ZSOR is "Billing Type" while DN is "DR type"

[btparkhe1920]

Debit Note

[AliGW]

Neither of us is able to understand what you have given us. You need to be prepared to explain and provide a workbook that matches what you are telling us.

What is "Debit Note" meant to tell us?

[btparkhe1920]

I have 2 cells
D1 = Days1 ( Clearing Date - Int Date )

D2 = Days2 ( Clearing Date - Int Date - If there are more recaps against a one sale bill, then less days)

Give an example.
If the sale bill is done on 04-07-2021. So the first 8 days will be interest-free. 9 to 15 days will be in 10%, 16 to 60 days will be in 13% and 61 days will be in 16%.

sales Bill Date Int Date Debit Amount Cleaing Date Remark Days Calculation 10% 13% 16%
04-07-21 12-07-21 10000 30-Jun-21 Advance Payment -12 Nil
04-07-21 12-07-21 10000 15-Jul-21 Payment received after int date 3 8 Less than 15
04-07-21 12-07-21 10000 25-Jul-21 Payment received after int date 13 19 21 > 15 <60
04-07-21 12-07-21 10000 25-Sep-21 Payment received after int date 75 19 160 66 >61

[JohnTopley]

See attached:

Table in M2:O4 (see http://www.mcgimpsey.com/excel/variablerate.html for explanation)

=ROUND(SUMPRODUCT(($F2>=$M$2:$M$4)*($F2-$M$2:$M$4),$O$2:$O$4)/365*$C2,0)

I believe Calculation in I5 is incorrect

=ROUND(C5*I1/365*15,0) should be =ROUND(C5*I1/365*23,0)

Difference between J4 & L4 is due ROUNDing of individual values (J4) vs ROUNDing of total (L4)

[btparkhe1920]

please attachment file

[JohnTopley]

Formula is wrong: you are multiplying by a DATE (column C) not value (Column I)

=ROUND(SUMPRODUCT(($R5>=$Z$5:$Z$7)*($R5-$Z$5:$Z$7),$AB$5:$AB$7)/365*$I5,0)

And I do not understand your logic which to me is inconsistent: why for Rows 8 and 9 is there no 10% entry?

[btparkhe1920]

document number Is the same ( column E6 to e9)

[JohnTopley]

I have add a table & formula for "DN". I don't understand the calculations for the same "doc no".

These are the calculations (as far as I can determine)

T8

=$I$8*0.13/365*15 ... Why 15 (Q8 or R9-R8) ??

T9

=$I9*0.13/365*24 ... why 24 (Q9-R8) ??

U9

=$I9*0.16/365*28 ...why 28 (R9-Q9) ??

[btparkhe1920]

Sir, in accounting language, DN means for the debit note,

If there is a debit note, interest calculation will be required from 1 day

[JohnTopley]

No explanation to my query in post #11.

[btparkhe1920]

If there are similar bills and there are more than one receipt against it but its date is different

Example


Therefore, you have already paid 21 days interest. Then the previous 13 days will be less than 28 days

[JohnTopley]

I cannot think of a formula-based solution to the situation where there are multiple payments for a given Document Number so I will leave this to other contributors to offer a solution.