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<=1 30,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
---------------
Jacob Skaria


"Nicholas1" wrote:

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<=1 30,SUM(H4*0.00036),IF(I4=131,SUM(I4*0.00048)))))

Help

Thanks

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)



"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"Nicholas1" wrote:

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<=1 30,SUM(H4*0.00036),IF(I4=131,SUM(I4*0.00048)))))

Help

Thanks