Excel formula for inventory problem

[Neal]

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)))

 

[Fred Smith]

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]

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.

 

[JBeaucaire]

=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)*.....

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.