so i have a spreadsheet for keeping track of a product we produce daily. However; each day a different "value" is used for the product. So I need to know what is the average value for the amount of product shipped each day. The problem i'm having is only averaging the the exact amount of previous production to equal whats in inventory. Example below, the inventory on 8/11/11 was produced between 8/7 and 8/11 but only about 860 of the 1304 gallons produced on the 7th should count towards the average.
Is this something I'll have to make a UDF for? I am a VB scrub, so i doubt i'd be able too. When hand calculated I come up with an average value/unit on 8/11/11 of 0.040196.
I feel like i've explained this problem poorly but i don't know how else to break it down. I apologize in advance for all the wrong things i've done.
| Produced | Inventory | Shipped | Value
8/7/2011 | 1,304.03 | 23,320.73 | 1,347.00 | 0.038903
8/8/2011 | 10,209.08 | 32,182.81 | 3,584.00 | 0.040609
8/9/2011 | 9,782.51 | 38,381.31 | 7,746.00 | 0.039880
8/10/2011 | - | 30,635.31 | 9,783.00 |
8/11/2011 | - | 20,852.31 | 5,337.00 |
8/12/2011 | 18,509.79 | 34,025.10 | 11,090.00 | 0.041364
8/13/2011 | 1,357.80 | 24,292.90 | 944.00 | 0.040291
8/14/2011 | 9,713.56 | 33,062.46 | 1,027.00 | 0.038460
8/15/2011 | 1,646.06 | 33,681.52 | 3,345.36 | 0.037685
8/16/2011 | 2,111.14 | 32,447.30 | 9,453.01 | 0.040724
8/17/2011 | - | 22,994.29 | 9,309.27 |
8/18/2011 | - | 13,685.02 | 5,044.69 |
8/19/2011 | 20,855.34 | 29,495.67 | 3,875.88 | 0.039846
8/20/2011 | 3,219.24 | 28,839.03 | 4,055.07 | 0.036106
8/21/2011 | 1,999.27 | 26,783.23 | 531.48 | 0.038507
8/22/2011 | 11,540.03 | 37,791.78 | 4,686.25 | 0.037963
8/23/2011 | 11,905.30 | 45,010.83 | 6,472.50 | 0.048929
8/24/2011 | - | 38,538.33 | 14,389.12 |
8/25/2011 | 11,140.26 | 35,289.47 | 4,288.34 | 0.054406
8/26/2011 | 16,088.41 | 47,089.55 | 8,899.64 | 0.037618
8/27/2011 | 4,796.25 | 42,986.15 | 1,935.62 | 0.037226
8/28/2011 | 3,852.32 | 44,902.85 | 1,774.43 | 0.037562
8/29/2011 | 2,941.71 | 46,070.13 | 5,892.66 | 0.037618
8/30/2011 | 1,238.60 | 41,416.07 | 3,940.52 | 0.037785
8/31/2011 | - | 37,475.54 | 10,464.77 |
9/1/2011 | 34,360.08 | 61,370.85 | 19,144.39 | 0.059624
9/2/2011 | 9,553.95 | 51,780.41 | - | 0.038586
Is this something I'll have to make a UDF for? I am a VB scrub, so i doubt i'd be able too. When hand calculated I come up with an average value/unit on 8/11/11 of 0.040196.
I feel like i've explained this problem poorly but i don't know how else to break it down. I apologize in advance for all the wrong things i've done.
| Produced | Inventory | Shipped | Value
8/7/2011 | 1,304.03 | 23,320.73 | 1,347.00 | 0.038903
8/8/2011 | 10,209.08 | 32,182.81 | 3,584.00 | 0.040609
8/9/2011 | 9,782.51 | 38,381.31 | 7,746.00 | 0.039880
8/10/2011 | - | 30,635.31 | 9,783.00 |
8/11/2011 | - | 20,852.31 | 5,337.00 |
8/12/2011 | 18,509.79 | 34,025.10 | 11,090.00 | 0.041364
8/13/2011 | 1,357.80 | 24,292.90 | 944.00 | 0.040291
8/14/2011 | 9,713.56 | 33,062.46 | 1,027.00 | 0.038460
8/15/2011 | 1,646.06 | 33,681.52 | 3,345.36 | 0.037685
8/16/2011 | 2,111.14 | 32,447.30 | 9,453.01 | 0.040724
8/17/2011 | - | 22,994.29 | 9,309.27 |
8/18/2011 | - | 13,685.02 | 5,044.69 |
8/19/2011 | 20,855.34 | 29,495.67 | 3,875.88 | 0.039846
8/20/2011 | 3,219.24 | 28,839.03 | 4,055.07 | 0.036106
8/21/2011 | 1,999.27 | 26,783.23 | 531.48 | 0.038507
8/22/2011 | 11,540.03 | 37,791.78 | 4,686.25 | 0.037963
8/23/2011 | 11,905.30 | 45,010.83 | 6,472.50 | 0.048929
8/24/2011 | - | 38,538.33 | 14,389.12 |
8/25/2011 | 11,140.26 | 35,289.47 | 4,288.34 | 0.054406
8/26/2011 | 16,088.41 | 47,089.55 | 8,899.64 | 0.037618
8/27/2011 | 4,796.25 | 42,986.15 | 1,935.62 | 0.037226
8/28/2011 | 3,852.32 | 44,902.85 | 1,774.43 | 0.037562
8/29/2011 | 2,941.71 | 46,070.13 | 5,892.66 | 0.037618
8/30/2011 | 1,238.60 | 41,416.07 | 3,940.52 | 0.037785
8/31/2011 | - | 37,475.54 | 10,464.77 |
9/1/2011 | 34,360.08 | 61,370.85 | 19,144.39 | 0.059624
9/2/2011 | 9,553.95 | 51,780.41 | - | 0.038586
