HOW TO CALCULATE

[anshad1]

how to work the below

Please help as below details for me

the calculation to be starting from 6 day (frist 5 days for free
charges)
minimum charges to be 75.00 per cbm
(FOR EX: 1800.000 KGS / 3.000 CBM X 8 = 75.00 ( calculation is
24.00, but i am charging minimum 75.00)
3000.000 KGS / 12.000 CBM X 8= 96.00
also if weight is 8500 kgs & cbm is 3.500 then will charge to weight
charges as 8.500 cbm (8.500 x 8 = 68.00, which is higher.


1 TO 5 DAYS 8 PER W/M 8.00
6 TO 10 DAYS 13 PER W/M 21.00
11 TO 15 DAYS 18 PER W/M 39.00
16 TO 20 DAYS 23 PER W/M 62.00
21 TO 25 DAYS 28 PER W/M 90.00
26 TO 30 DAYS 33 PER W/M 123.00
31 TO 35 DAYS 38 PER W/M 161.00
36 TO 40 DAYS 43 PER W/M 204.00
41 TO 45 DAYS 48 PER W/M 252.00
46 TO 50 DAYS 48 PER W/M 300.00
51 TO 55 DAYS 48 PER W/M 348.00
56 TO 60 DAYS 50 PER W/M 398.00
61 TO 65 DAYS 50 PER W/M 448.00
66 TO 70 DAYS 50 PER W/M 498.00
71 TO 75 DAYS 50 PER W/M 548.00
76 TO 80 DAYS 50 PER W/M 598.00
81 TO 85 DAYS 50 PER W/M 648.00
86 TO 90 DAYS 55 PER W/M 703.00
91 TO 95 DAYS 55 PER W/M 758.00
96 TO 100 DAYS 55 PER W/M 813.00
101 TO 105 DAYS 55 PER W/M 868.00
106 TO 110 DAYS 55 PER W/M 923.00
111 TO 115 DAYS 55 PER W/M 978.00
116 TO 120 DAYS 60 PER W/M 1038.00
121 TO 125 DAYS 60 PER W/M 1098.00
126 TO 130 DAYS 60 PER W/M 1158.00
131 TO 135 DAYS 60 PER W/M 1218.00
136 TO 140 DAYS 60 PER W/M 1278.00
141 TO 145 DAYS 60 PER W/M 1338.00
146 TO 150 DAYS 65 PER W/M 1403.00

Regards
Anshad

 

[Joel]

I don't completely understand where all you numbers (charge to weight) in the
example come from and I don't know what is the 3rd column in the table you
provided.

You need to have some kind of IF equation that has the number of Days and
the CBM as inputs.
Days in cell F1
CBM in cell G1
=if(F1<=5,0,if((G1*8)<75,'some formula with 75 as rate','some formula with
actual rate')


To lookup days use the process below

With Days being in row 1 and "1 8 8" being in Row 2 you can use the
formula below to look up the rates in the table. A2:C25 is the entire table
and the last 2 is the column B with the W/M rate.
=VLOOKUP(7,A2:C25,2)

Days W/M
1 8 8
6 13 21
11 18 39
16 23 62
21 28 90
26 33 123
31 38 161
36 43 204
41 48 252
46 48 300
51 48 348
56 50 398
61 50 448
66 50 498
71 50 548
76 50 598
81 50 648
86 55 703
91 55 758
96 55 813
101 55 868
106 55 923
111 55 978
116 60 1038