Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel formula for inventory problem
I am unable to figure out how to get the formula below to work properly. It
is for figuring out the amount of product sold for the year where B3=cost of item,C3=starting inventory on Jan 1st, D3=inventory purchased between Jan. 1st and 3/31, E3=inventory on 3/31, F3=inventory purchased between Apr. 1st and 6/30, G3=inventory on 6/30, H3=inventory purchased between Jul. 1st and 9/30, I3=inventory on 9/30, J3=inventory purchased between Oct. 1st and 12/30, and K3=ending inventory on 12/31. The formula help for excel is very poor and confusing. It will not actually tell me what the specific problem is, nor how to fix it. =SUM(B3*(C3+D3(-(IF(AND(E3=0,G3<0),E3,0))),(IF(AND(G3=0,I3<0),(+ F3-G3),0)),(IF(AND(I3=0,K<0),(+F3+H3-I3),0)),(IF(K3=0),(+F3+H3+J3-K3),0))) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel formula for inventory problem
The first thing to do with any Excel problem is figure out how to do it
manually. Then you can translate it into Excel. In your case, Sales = OpeningInventory + Purchases - ClosingInventory Now translate this into Excel: =c3+d3-e3 And extend for the entire year: =c3+d3-e3+e3+f3-g3+g3+h3-i3+i3+j3-k3 The intermediate inventories cancel out, so your final formula would be: =c3+d3+f3+h3+j3-k3 It looks to me that your formula is trying to handle cases where the inventory count is zero. You'll need to decide whether that's relevant, and if so, adjust your requirements accordingly. Regards, Fred "Neal" wrote in message ... I am unable to figure out how to get the formula below to work properly. It is for figuring out the amount of product sold for the year where B3=cost of item,C3=starting inventory on Jan 1st, D3=inventory purchased between Jan. 1st and 3/31, E3=inventory on 3/31, F3=inventory purchased between Apr. 1st and 6/30, G3=inventory on 6/30, H3=inventory purchased between Jul. 1st and 9/30, I3=inventory on 9/30, J3=inventory purchased between Oct. 1st and 12/30, and K3=ending inventory on 12/31. The formula help for excel is very poor and confusing. It will not actually tell me what the specific problem is, nor how to fix it. =SUM(B3*(C3+D3(-(IF(AND(E3=0,G3<0),E3,0))),(IF(AND(G3=0,I3<0),(+ F3-G3),0)),(IF(AND(I3=0,K<0),(+F3+H3-I3),0)),(IF(K3=0),(+F3+H3+J3-K3),0))) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel formula for inventory problem
Fred,
I'm trying to account for what time of the year it is and figure total sales YTD, using the most recent inventory figure in the calculation. If it were mid-year, teh 3rd adn 4th Quarter cells would be blank and the formula would be: B3*(C3+D3+F3-G3) If it were the end of the year it would be: B3*(C3+D3+F3+H3+J3-K3) I need excel to figure which inventory to use and how many of the inventory purchased cells to add in. "Fred Smith" wrote: The first thing to do with any Excel problem is figure out how to do it manually. Then you can translate it into Excel. In your case, Sales = OpeningInventory + Purchases - ClosingInventory Now translate this into Excel: =c3+d3-e3 And extend for the entire year: =c3+d3-e3+e3+f3-g3+g3+h3-i3+i3+j3-k3 The intermediate inventories cancel out, so your final formula would be: =c3+d3+f3+h3+j3-k3 It looks to me that your formula is trying to handle cases where the inventory count is zero. You'll need to decide whether that's relevant, and if so, adjust your requirements accordingly. Regards, Fred "Neal" wrote in message ... I am unable to figure out how to get the formula below to work properly. It is for figuring out the amount of product sold for the year where B3=cost of item,C3=starting inventory on Jan 1st, D3=inventory purchased between Jan. 1st and 3/31, E3=inventory on 3/31, F3=inventory purchased between Apr. 1st and 6/30, G3=inventory on 6/30, H3=inventory purchased between Jul. 1st and 9/30, I3=inventory on 9/30, J3=inventory purchased between Oct. 1st and 12/30, and K3=ending inventory on 12/31. The formula help for excel is very poor and confusing. It will not actually tell me what the specific problem is, nor how to fix it. =SUM(B3*(C3+D3(-(IF(AND(E3=0,G3<0),E3,0))),(IF(AND(G3=0,I3<0),(+ F3-G3),0)),(IF(AND(I3=0,K<0),(+F3+H3-I3),0)),(IF(K3=0),(+F3+H3+J3-K3),0))) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel formula for inventory problem
Neal;160745 Wrote: =SUM(B3*(C3+D3(-(IF(AND(E3=0,G3<0),E3,0))),(IF(AND(G3=0,I3<0),(+ F3-G3),0)),(IF(AND(I3=0,K<0),(+F3+H3-I3),0)),(IF(K3=0),(+F3+H3+J3-K3),0))) Well, there's a TYPO in there, ... *(IF(AND(I3=0,K_3_<0)*..... Neal;160779 Wrote: If it were mid-year, the 3rd and 4th Quarter cells would be blank and the formula would be: * B3*(C3+D3+F3-G3) * If it were the end of the year it would be: *B3*(C3+D3+F3+H3+J3-K3)* No, the second formula would be the one you use all year long. If the later quarters are empty, they add zero, no harm done, and it always reflects the current status of things based on the numbers all across row3. Fred is right. -- JBeaucaire ------------------------------------------------------------------------ JBeaucaire's Profile: http://www.thecodecage.com/forumz/member.php?userid=73 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=44683 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel IF Formula for an inventory... | Excel Discussion (Misc queries) | |||
Formula needed for too much inventory | Excel Worksheet Functions | |||
inventory comparison formula?? | Excel Worksheet Functions | |||
Need help with a formula in inventory | Excel Discussion (Misc queries) | |||
How can i get an inventory list that adds and subtracts inventory | Excel Discussion (Misc queries) |