Formula Help
 

Item I.D. Cost Total Packs
a 200 136540
b 200 133530
c 200 72920
d 200 40340
e 200 290120
f 200 471520
g 200 0


I could do with some help if any one could! I need to find the proportionate
cost of each item. For example if the total packs of A-G was 100 and A alone
was 50 it would recieve half of the cost (100). However, even though g says
it is 0 i need to allocate 1% of the cost to this item and making all the
other costs still = 200. I have an easy formula should the 0 not occur (in
other sheets) but i can't figure out how to do this.

Help would be HUGELY appriciated!!! Thanks!

Formula Help
 

Your execution would look like this if X is the number of items that were 0

Y = (100 percent - (X * Number of zeroes)) * (Item Cost/ Total Cost)


=(100 - COUNTIF(C$1:C$100,0))*(C1/SUM(C$1:C$100))

to put 1% in the cells that are 0 make the follwoing modification

=if(C1=0,1,(100 - COUNTIF(C$1:C$100,0))*(C1/SUM(C$1:C$100)))




"teo410" wrote:

Item I.D. Cost Total Packs
a 200 136540
b 200 133530
c 200 72920
d 200 40340
e 200 290120
f 200 471520
g 200 0


I could do with some help if any one could! I need to find the proportionate
cost of each item. For example if the total packs of A-G was 100 and A alone
was 50 it would recieve half of the cost (100). However, even though g says
it is 0 i need to allocate 1% of the cost to this item and making all the
other costs still = 200. I have an easy formula should the 0 not occur (in
other sheets) but i can't figure out how to do this.

Help would be HUGELY appriciated!!! Thanks!

Formula Help
 

Great Stuff!! Couple of questions.

a) What if there is more than 1 cell with a 0?
b) How can I copy this into another sheet, or even the same sheet with
different values in different cells?

Thanks again

"Joel" wrote:

Your execution would look like this if X is the number of items that were 0

Y = (100 percent - (X * Number of zeroes)) * (Item Cost/ Total Cost)


=(100 - COUNTIF(C$1:C$100,0))*(C1/SUM(C$1:C$100))

to put 1% in the cells that are 0 make the follwoing modification

=if(C1=0,1,(100 - COUNTIF(C$1:C$100,0))*(C1/SUM(C$1:C$100)))




"teo410" wrote:

Item I.D. Cost Total Packs
a 200 136540
b 200 133530
c 200 72920
d 200 40340
e 200 290120
f 200 471520
g 200 0


I could do with some help if any one could! I need to find the proportionate
cost of each item. For example if the total packs of A-G was 100 and A alone
was 50 it would recieve half of the cost (100). However, even though g says
it is 0 i need to allocate 1% of the cost to this item and making all the
other costs still = 200. I have an easy formula should the 0 not occur (in
other sheets) but i can't figure out how to do this.

Help would be HUGELY appriciated!!! Thanks!

Formula Help
 

Count if will return the number of zeroes so the formula already handles more
than one cell with zeroes. The X in my explaination is countIF.

When copying the formula you need to make the usual changes you would make
when copyiing any formula. he Range C$1:C$100 need to be changged in two
places.

"teo410" wrote:

Great Stuff!! Couple of questions.

a) What if there is more than 1 cell with a 0?
b) How can I copy this into another sheet, or even the same sheet with
different values in different cells?

Thanks again

"Joel" wrote:

Your execution would look like this if X is the number of items that were 0

Y = (100 percent - (X * Number of zeroes)) * (Item Cost/ Total Cost)


=(100 - COUNTIF(C$1:C$100,0))*(C1/SUM(C$1:C$100))

to put 1% in the cells that are 0 make the follwoing modification

=if(C1=0,1,(100 - COUNTIF(C$1:C$100,0))*(C1/SUM(C$1:C$100)))




"teo410" wrote:

Item I.D. Cost Total Packs
a 200 136540
b 200 133530
c 200 72920
d 200 40340
e 200 290120
f 200 471520
g 200 0


I could do with some help if any one could! I need to find the proportionate
cost of each item. For example if the total packs of A-G was 100 and A alone
was 50 it would recieve half of the cost (100). However, even though g says
it is 0 i need to allocate 1% of the cost to this item and making all the
other costs still = 200. I have an easy formula should the 0 not occur (in
other sheets) but i can't figure out how to do this.

Help would be HUGELY appriciated!!! Thanks!

Formula Help
 

Thanks Joel! Been a massive help

"Joel" wrote:

Count if will return the number of zeroes so the formula already handles more
than one cell with zeroes. The X in my explaination is countIF.

When copying the formula you need to make the usual changes you would make
when copyiing any formula. he Range C$1:C$100 need to be changged in two
places.

"teo410" wrote:

Great Stuff!! Couple of questions.

a) What if there is more than 1 cell with a 0?
b) How can I copy this into another sheet, or even the same sheet with
different values in different cells?

Thanks again

"Joel" wrote:

Your execution would look like this if X is the number of items that were 0

Y = (100 percent - (X * Number of zeroes)) * (Item Cost/ Total Cost)


=(100 - COUNTIF(C$1:C$100,0))*(C1/SUM(C$1:C$100))

to put 1% in the cells that are 0 make the follwoing modification

=if(C1=0,1,(100 - COUNTIF(C$1:C$100,0))*(C1/SUM(C$1:C$100)))




"teo410" wrote:

Item I.D. Cost Total Packs
a 200 136540
b 200 133530
c 200 72920
d 200 40340
e 200 290120
f 200 471520
g 200 0


I could do with some help if any one could! I need to find the proportionate
cost of each item. For example if the total packs of A-G was 100 and A alone
was 50 it would recieve half of the cost (100). However, even though g says
it is 0 i need to allocate 1% of the cost to this item and making all the
other costs still = 200. I have an easy formula should the 0 not occur (in
other sheets) but i can't figure out how to do this.

Help would be HUGELY appriciated!!! Thanks!