inventory tracking

[maradykstra]

Hi,

I'm trying to keep inventory of some things that I record in a table. I want one cell that will show me my current inventory everytime I record my inventory in the table. Here is an example:


C3: =If(C14="oranges",(100-D14),IF=(C14="apples",(500-D14)))


Where my value 100 and 500 is my starting quantity of oranges and apples respectively. My formula works for that one row but I'm trying to make it work for the next row in my table when I add my data (C15:C20 and D15:D20 or how ever long the table is)


The formula has to add up the qty of oranges or apples I put in the table and then subtract that from my starting inventory. I'm sure there are other ways of keeping track of inventory but I thought this was a good way to see right away what my current inventory is after I input my data in a table. I'm open to other ways too.


Thank you for your help.

 

[Hui]

JaElle

Is every row a date or sales quantity ?

Do you have a Column C with values "Apples", "Oranges" etc


If you setup an opening inventory for Apples Say in H1 and Oranges in H2

something like this in C3 will do Apples

=H1-Sumproduct(+1*((C14:C20)="Apples"),(D14:D20))


and in D3 will do Oranges

=H2-Sumproduct(+1*((C14:C20)="Oranges"),(D14:D20))

adjust ranges to suit

 

[maradykstra]

Hui,

Thank you so much for that. It worked great. Can you explain to me the sumproduct formula and what that +1 means in the formula?

Thanks again.

 

[Hui]

JaElle

Have a read of

http://chandoo.org/wp/2009/11/10/excel-sumproduct-formula/

and the comments at the bottom of the article

 

[maradykstra]

Thanks Hui. So do you have every excel function memorized? As I was doing this, a few functions crossed my mind...If function, vlookup, sum...etc. Would you say the more functions that one is familiar with the more options and best options you can choose to solve the problem? I didn't even think of the sumproduct, but now I'm familiar with it thanks to you. I guess it's a matter of learning them. Did it take you long to learn all the functions?

 

[oldchippy]

Hi JaElle,


Here's a list of Excel functions explaining what each one does.


http://office.microsoft.com/en-us/excel/HP100791861033.aspx

 

[Hui]

JaElle

Not at all,

But I do remember all the common ones for looking up and manipulating data, dates, text, logical and mathematics

but not specific scientfic, financial or statistical functions

and there are others which I never plan on using as they are way too specialized


If you are going to do a lot of Excel work you need to have a good backing in looking up and manipulating data, date, logical, text, and then enough about either financial,

engineering or what ever your specialist field is to serve your business.


I recomend you have a look at some of the common excel books, they talk about how to retrieve, manipulate and present data, they don't go into the intricacies of say the Weibull function.


Chandoo book is a good start http://chandoo.org/wp/shop/


The other thing is learn how to use resources at hand,

+ The Excel Help is a great resource, once you work out what to look for and understand the format the answer is presented in,

+ Google is great for answering nearly everything.

+ Keep a list of common web sites which you can use for examples and further references.


Also have a read of this post

http://chandoo.org/wp/2010/05/10/becoming-excel-expert/

where the same topic is being discussed at present

 

[maradykstra]

Hui,

Thanks for all that information. I'm actually utilizing almost everything you listed. I also am enrolled in Chandoo's excel school. It's been so helpful. I love it. I think the hardest part is remembering the formulas. It seems as if there a few ways in obtaining the answers. I think I need to practice and practice so I get used to the formulas. Thank you for always taking the time answer my questions. I'm always so amazed at your knowledge and your prompt response.


Thanks again.

JaElle