Incremental charges

  • Thread starter Thread starter Nicholas1
  • Start date Start date
N

Nicholas1

I need to be able to show incremental charges over a period.

To make things simple say I had a product rented at £100 for 120 days the
charges are as follows

0 - 5 days = Free of charge
6 to 25 days = 0.024% of product value
26 to 130 days = 0.036% of product value
131+ = 0.048%

The columns are

A - Product B - Value C - Rental Start Date D - Days rented

I've got the If calclautions but it gives a total of the 26 to 130 days from
day 1:

=IF(I4<=5,"FOC",IF(I4<=25,SUM(H4*0.00024),IF(I4<=130,SUM(H4*0.00036),IF(I4>=131,SUM(I4*0.00048)))))

Help

Thanks
 
In your formula I hope Days is in I5 and value is in H5...Try the below

=IF(I5<=5,"FOC",
IF(I5<=25,(I5-5)*0.00024*H5,
IF(I5<=130,(20*0.00024*H5)+((I5-25)*0.00036*H5),
IF(I5>=131,(20*0.00024*H5)+((105*0.00036*H5)+((I5-130)*0.00048*H5))))))

If this post helps click Yes
 
Thanks for that, it works. On reading what you've posted it, it all seems so
logical now, my brain just would not think (Monday mornings)
 
Back
Top