breaking down a whole number into pre-defined parts..

[GD]

Hey, i've got an issue whereby a total stock figure, in terms of pricing how
much to paid for storage, needs to be broken down. If for example in Cell A1
is the figure 247,000 and the weighting is the first 100,000 is priced at 10p
per tonne, the second 100,000 at 7p per tonne, the 3rd 100,000 at 5 pr tonne
and so-on. What i'm looking for is a formula of sorts that can go in three
cells to separate the 247,000 into a 100,000, a 100,000 and a 47,00...if you
get what I mean?

Otherwise it means having to manually key the figures into pre-defined cells
to make the calculation run, it would be really handy if there was function
to do it automatically

 

[Sean Timmons]

in B1, =if(A1>=100000,1000000,A1*10)
in C1, =if(A1>200000,700000,if(B1=1000000,(A1-100000)*7,0))
in D1, =if(C1=1000000,if(C1=0,0,(A1-200000)*5))

 

[MartinW]

Hi GD,

You may want to take a look here at John McGimpsey's site.
You should be able to adapt his methods to your needs.

http://www.mcgimpsey.com/excel/variablerate.html


HTH
Martin

 

[GD]

Thanks to you both

Hi GD,

You may want to take a look here at John McGimpsey's site.
You should be able to adapt his methods to your needs.

http://www.mcgimpsey.com/excel/variablerate.html


HTH
Martin

 

[GD]

Hi, i've only just had chance to implement this - the result is B1 = 1000000,
C1 = 700000 and D1 = 'FALSE' ?

Not sure on this?