Simple Stock / Sales Forumula

Discuss the spreadsheet application

Simple Stock / Sales Forumula

Postby Gazza2013 » Mon Oct 07, 2013 12:36 pm

Hello Guys!

I'm a bit of a newbie at this but need just a simple formula for a stock levels sheet I use.

Lets say column E is the quantity in stock of each item
Column F will be the amount of sales of that item on a particular website.

So item 1 E2 has Qty 10 in stock
I have 2 sales of this item

Is it possible for me to just tap the number 2 into E2 and have that figure deducted from E2 so it equals 8 and then have that number increased to F2 by 2

It's so I can easily just hit the numbers on the keyboard and all this is done automatically for me, at the moment I am manually going to each cell and deducting the number, obviously I might start to make errors whilst doing this...

Even better would be that if the QTY reaches say 2 it will turn to red text as well!

That would be awesome if possible!

I hope I have made sense.

Thanks Guys! :D
Openoffice 4.0
Gazza2013
 
Posts: 4
Joined: Mon Oct 07, 2013 12:21 pm

Re: Simple Stock / Sales Forumula

Postby Villeroy » Mon Oct 07, 2013 1:51 pm

purchases in one column, sales in another column.
=SUM(purchase)-SUM(sales)+STYLE(IF(CURRENT()<3;"red style"))
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 12.04, Apache OpenOffice 4.x
User avatar
Villeroy
Volunteer
 
Posts: 17238
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Simple Stock / Sales Forumula

Postby Gazza2013 » Mon Oct 07, 2013 4:56 pm

Hi Thanks

Would this deduct the number of sales in the QTY column?

So I have 10 of widget1
I sell 2 in a day
I just tap 2 into the cell with the QTY 10 and the figure would be reduced to 8, and also the sales total cell would increase by 2.

It's just so I can keep track of stock and monitor how many sales I am achieving in each item and give me a warning if I am running low.

Do I have to label the columns “purchase” and “sales”?

I'm not sure If I am making myself clear as I have little experience with these types of documents.
Openoffice 4.0
Gazza2013
 
Posts: 4
Joined: Mon Oct 07, 2013 12:21 pm

Re: Simple Stock / Sales Forumula

Postby Villeroy » Mon Oct 07, 2013 5:16 pm

download/file.php?id=14074

Tools>Options>Calc>Calculation... all check boxes OFF except for "... match whole cell"=ON
purchases in one column, sales in another column, item in A1
=SUMIF(purchase;A1)-SUMIF(sales;A1)+STYLE(IF(CURRENT()<3;"red style"))
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 12.04, Apache OpenOffice 4.x
User avatar
Villeroy
Volunteer
 
Posts: 17238
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Calc

Who is online

Users browsing this forum: No registered users and 30 guests