Formula Help

[teo410]

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!

 

[Joel]

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]

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]

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]

Thanks Joel! Been a massive 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.