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

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

"GD" wrote:

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

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" wrote in message
...
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

Thanks to you both

"MartinW" wrote:

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" wrote in message
...
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

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

Not sure on this?

"Sean Timmons" wrote:

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

"GD" wrote:

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