Inventory/Running Total formula (IF formula?)

[MikeDavalos]

Hi all,

First time poster. I'm a novice when it comes to excel, so please forgive my amateur knowledge on excel ...

I have an excel sheet that shows deliveries in and out. I would like to create a new column that would have a running total/inventory.

[TABLE="class: grid, width: 400, align: left"]
<tbody>[TR]
[TD]Units[/TD]
[TD]In/Out[/TD]
[TD]Total on Hand[/TD]
[/TR]
[TR]
[TD]140[/TD]
[TD]IN[/TD]
[TD]140[/TD]
[/TR]
[TR]
[TD]140[/TD]
[TD]IN[/TD]
[TD]280[/TD]
[/TR]
[TR]
[TD]150[/TD]
[TD]OUT[/TD]
[TD]130[/TD]
[/TR]
[TR]
[TD]148[/TD]
[TD]IN[/TD]
[TD]278[/TD]
[/TR]
[TR]
[TD]100[/TD]
[TD]OUT[/TD]
[TD]178
[/TD]
[/TR]
</tbody>[/TABLE]











I believe I have an idea of how it's supposed to work.. with the IF commands, but no clue at all how to put it into place. Any help would be greatly appreciated.

P.S. just to be clear.. If IN/OUT column says "IN" then "Total on hand" needs to add the previous amount from the A column, if it's an "OUT" then it needs to subtract it automatically. Thanks!

 

[JTPigskin]

=IF(B3="IN",C2+A3,C2-A3)

=IF(B4="IN",C3+A4,C3-A4)

=IF(B5="IN",C4+A5,C4-A5)

=IF(B6="IN",C5+A6,C5-A6)

<colgroup><col style="mso-width-source:userset;mso-width-alt:768;width:32pt" width="42"> <col style="mso-width-source:userset;mso-width-alt:877;width:36pt" width="48"> <col style="mso-width-source:userset;mso-width-alt:1609;width:66pt" width="88"> <col style="mso-width-source:userset;mso-width-alt:3072;width:126pt" width="168"> </colgroup><tbody>
[TD="class: xl63, width: 42"]Units [/TD]
[TD="class: xl63, width: 48"]In/Out [/TD]
[TD="width: 88"]Total on Hand[/TD]
[TD="width: 168"][/TD]

[TD="class: xl65"]140[/TD]
[TD="class: xl63"]IN [/TD]
[TD="class: xl66"]140[/TD]
[TD="class: xl64"]=+C2[/TD]

[TD="class: xl65"]140[/TD]
[TD="class: xl63"]IN[/TD]
[TD="class: xl66"]280[/TD]

[TD="class: xl65"]150[/TD]
[TD="class: xl63"]OUT [/TD]
[TD="class: xl66"]130[/TD]

[TD="class: xl65"]148[/TD]
[TD="class: xl63"]IN[/TD]
[TD="class: xl66"]278[/TD]

[TD="class: xl65"]100[/TD]
[TD="class: xl63"]OUT [/TD]
[TD="class: xl66"]178[/TD]

</tbody>

[TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="class: xl193, width: 64"][/TD]
[/TR]
</tbody>[/TABLE]

 

[JTPigskin]

Also, don't underestimate the usefulness of the "insert function" key right to the left of your formula edit field. It is good for helping you build the basics for most formulas.

 

[MikeDavalos]

That worked perfect, thank you!