Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|