Spreadsheet: 830898d1685218133-need-help-to-find-out-code-wise-rate-calculation-code-wise-rate-calculation
Sheet: AA

 | Code | From Date | To Date | Days | Connected Load | Load | Rate/Month | Calculation method used for reference | Remarks
 | AG | 2022-01-01 00:00:00 |  |  |  | 19 |  | (20*I2)*G2/30 | 20 is the rate for AG category for the from and to date time frame, 30 is the days for one month
 | AG | 2022-03-28 00:00:00 |  |  |  | 40 |  | (20*I3)*G3/30 | 20 is the rate for AG category for the from and to date time frame, 30 is the days for one month
 | AG | 2022-07-23 00:00:00 | 2022-09-25 00:00:00 |  |  | 6 |  | (20*I4)*G4/30 | 20 is the rate for AG category for the from and to date time frame, 30 is the days for one month
 | AG | 2022-07-23 00:00:00 | 2022-09-25 00:00:00 |  |  | 60 |  | (20*I5)*G5/30 | 20 is the rate for AG category for the from and to date time frame, 30 is the days for one month
 | AG | 2022-09-01 00:00:00 | 2022-11-02 00:00:00 | 62 |  | 75 |  | ((10*I6*28)+(5*I6*34))/30 | 10 and 5 is the rate for AG category for the from and to date time frame respectively, 28 days are for rate 10 and 34 days are for rate 5. 30 is the days for one month
 | AG | 2022-12-01 00:00:00 |  |  |  | 100 |  | ((10*E7*7.5)+(5*E7*(G7-7.5)))/30 | Here I have used load brake up as per sheet BB, you can see in sheet BB that there is rate 10 from load 1 to 7.5 and rate 5 7.51 to 999. So I have calculated here First 7.5 load at the rate 10 and then subtracted 7.5 from total load and then remaining load at the rate of 5
 | AG | 2022-12-01 00:00:00 |  |  |  | 22 |  | ((7.5*10*E8)+(5*E8*(G8-7.5)))/30 | Here I have used load brake up as per sheet BB, you can see in sheet BB that there is rate 10 from load 1 to 7.5 and rate 5 7.51 to 999. So I have calculated here First 7.5 load at the rate 10 and then subtracted 7.5 from total load and then remaining load at the rate of 5
 | AG | 2023-02-01 00:00:00 |  |  |  | 35 |  | ((7.5*10*E9)+(5*E9*(G9-7.5)))/30 | Here same logic applied as per above but here we have to keep one thing in mind, when you see in sheet BB you will see that in this from date to date time frame period there is in to date cells – symbol available, means these rates are presently active and its end date not yet came.
 | COM | 2022-01-11 00:00:00 |  |  |  | 10 |  | (50*G10*E10)/30 | here 50 is the rate for the from load 1 to load 10, 30 is the days of one month
 | COM | 2022-09-22 00:00:00 |  | 65 |  | 40 |  | ((10*50*E11)+(85*E11*(G11-10)))/30 | here 10 is load, 50 is rate for upto 10 load, 85 is rate for load greater than 10
 | COM | 2023-03-20 00:00:00 |  | 59 |  | 75 |  | ((10*50*E12)+(85*E12*(G12-10)))/30 | here 10 is load, 50 is rate for upto 10 load, 85 is rate for load greater than 10
 | DOM | 2022-07-11 00:00:00 |  | 60 |  | 2 |  | (15*E13)/30 | 15 is the rate as per date and load criteria in sheet BB
 | DOM | 2022-08-11 00:00:00 |  | 55 |  | 4.5 |  | (45*E14)/30 | 45 is the rate as per date and load criteria in sheet BB
 | IND | 2022-04-01 00:00:00 |  | 30 |  | 38 |  | (90*G15*E15)/30 | Here 90 is the rate as per sheet BB for the load upto 40
 | IND | 2022-04-01 00:00:00 |  | 30 |  | 54 |  | ((40*90*E16)+(130*E16*(G16-40)))/30 | Here 40 is the load and 90 is rate, 130 is rate from load 41 to 60
 | IND | 2022-04-01 00:00:00 |  | 30 |  | 88 |  | ((40*90*E17)+(130*E17*(60-40))+(195*E17*(G17-60)))/30 | Here 40 is the load and 90 is rate, 130 is rate from load 41 to 60, 195 is rate for 61 to 100 load
 | IND | 2022-04-01 00:00:00 |  | 30 | 96 | 74 |  | ((40*90*E18)+(130*E18*(60-40))+(195*E18*(G18-60))+(265*E18*(F18-G18)))/30 | Here 40 is the load and 90 is rate, 130 is rate from load 41 to 60, 195 is rate for 61 to 100 load. And 265 is the rate which applicable only if connected load (column F) is greater than load (column G)