Incremental increase in a formula

[robingSA]

Hi all,

I am wanting to multiply an order by a fixed cost (0.055c) up to an
order quantity of 13,000. Thereafter, for every FULL 500 units over
the 13,000 the cost will increase by 0.001c.

Examples:
1. Order = 13,000 - Cost = 715
2. Order = 14,000 - Cost = 798 (0.001*2 + 0.055 * 14,000)
3. Order = 14,400 - Cost = 798

How can I put this into a single formula?

Thanks in advance.
Rob

 

[robingSA]

I think this will work, where A1 contains the order quantity:

=IF(A1<=13000,0.055,(ROUNDDOWN((A1-13000)/500,0))*0.001+0.055)

 

[Roger Govier]

Hi Robin

Try
=A1*0.055+INT((A1-13000)/500)*500*0.001

Although I get results of 715, 771 and 793