Hello every one i need your help with a formula. Example Date Delivered Cost Sold Retail Price Profit Pepsi Coke Pepsi Coke Pepsi Coke Pepsi Coke Pepsi Coke 7/1/08 100 50 1.00 1.15 50 25 1.29 1.39 ???? ???? 7/2/08 0 40 15 1.19 1.29 ???? ???? 7/3/08 250 70 0.79 .85 60 45 0.99 1.09 ???? ???? 7/4/08 0 55 35 1.15 1.25 ???? ???? Every day delivery does not come. Each time cost is different. Every day i put sold and every day sold price is different. Now i need a formula that will find profit but it must use the old inventory first. until old inventory is all gone then start next one. The current formula i have right now is =(K5-E5)*H5 Please help me out i been working on this for about a month. Thank you very very much.

I'm haveing problems figuring out which data is in which columns so I wil just describe the answer. The solution is not perfect because you could have stolen or damaged bottles that are losses that eventually have to be deducted from the profits. Your reall profit is (total sold price) - (total purchase price) - (previous profits) + (adjustment) The adjustment is your initial investment or inventory. If you initially stock 10-0 bottles of coke the 1st day but only sold 5 bottles you make a profit on the 5 bottles. You can use sumproduct. If your data starts in row 2. for pepsi with the total purchase is for Row 1 =sumproduct(B$2:B2,D$22) Notice where the dollar sign is located. When yhou copy the formula down the row more rows are counted. "Summer" wrote: > Hello every one i need your help with a formula. > > Example > > Date Delivered Cost Sold Retail > Price Profit > Pepsi Coke Pepsi Coke Pepsi Coke Pepsi > Coke Pepsi Coke > 7/1/08 100 50 1.00 1.15 50 25 1.29 > 1.39 ???? ???? > 7/2/08 0 40 15 > 1.19 1.29 ???? ???? > 7/3/08 250 70 0.79 .85 60 45 0.99 > 1.09 ???? ???? > 7/4/08 0 55 35 > 1.15 1.25 ???? ???? > > Every day delivery does not come. Each time cost is different. > Every day i put sold and every day sold price is different. > > Now i need a formula that will find profit but it must use the old inventory > first. until old inventory is all gone then start next one. The current > formula i have right now is =(K5-E5)*H5 > > > Please help me out i been working on this for about a month. Thank you very > very much. >

Can i put the sample sheet on this Discussion group? "Joel" wrote: > I'm haveing problems figuring out which data is in which columns so I wil > just describe the answer. The solution is not perfect because you could have > stolen or damaged bottles that are losses that eventually have to be deducted > from the profits. > > Your reall profit is > > (total sold price) - (total purchase price) - (previous profits) + > (adjustment) > > The adjustment is your initial investment or inventory. If you initially > stock 10-0 bottles of coke the 1st day but only sold 5 bottles you make a > profit on the 5 bottles. > > You can use sumproduct. If your data starts in row 2. > > for pepsi with the total purchase is for Row 1 > > =sumproduct(B$2:B2,D$22) > > Notice where the dollar sign is located. When yhou copy the formula down > the row more rows are counted.

Email me at joel dot warburg at itt dot com "Summer" wrote: > Can i put the sample sheet on this Discussion group? > > "Joel" wrote: > > > I'm haveing problems figuring out which data is in which columns so I wil > > just describe the answer. The solution is not perfect because you could have > > stolen or damaged bottles that are losses that eventually have to be deducted > > from the profits. > > > > Your reall profit is > > > > (total sold price) - (total purchase price) - (previous profits) + > > (adjustment) > > > > The adjustment is your initial investment or inventory. If you initially > > stock 10-0 bottles of coke the 1st day but only sold 5 bottles you make a > > profit on the 5 bottles. > > > > You can use sumproduct. If your data starts in row 2. > > > > for pepsi with the total purchase is for Row 1 > > > > =sumproduct(B$2:B2,D$22) > > > > Notice where the dollar sign is located. When yhou copy the formula down > > the row more rows are counted.