Question

Locked

Question about Excel formula

By Kris Venugopalan ·
Hi

This is re. Stock control with spreadsheet. I have been trying to create an excel formula that will be applicable for all the scenarios given below:

Scenario 1
Opening Stock.............................10
Receipt to Store 1.....................200
Orders from members............500
Dispatch to members............210
Closing Stock at Store 1

Correct Answer.........................-290

Scenario 2
Opening Stock..............................0
Receipt to Store 1.........................100
Orders from members.................50
Dispatch to members.................50
Closing Stock at Store 1

Correct Answer.............................50

Scenario 3
Opening Stock.............................20
Receipt to Store 1........................50
Orders from members...............100
Dispatch to members..................70
Closing Stock at Store 1

Correct Answer.............................30

Scenario 4
Opening Stock.........................50
Receipt to Store 1....................100
Orders from members............50
Despatch to members...........50
Closing Stock at Store 1

Correct Answer........................100

Scenario 5
Opening Stock.........................-150
Receipt to Store 1.....................100
Orders from members.............50
Despatch to members...........100
Closing Stock at Store 1

Correct Answer........................-100

Trust I am clear. I am looking for help in arriving at ONE formula for all the above to arrive at the Closing Stock at Store 1

cheers

Kris

This conversation is currently closed to new comments.

5 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Answers

Collapse -

looks easy

by john.a.wills In reply to Question about Excel form ...

This looks too easy to be a real problem. You seem to have 4 input values and 1 output value (Closing stock). Put the input values in, say, a2, b2, c2 and d2 (with appropriate text in column 1). In f2 write "=" and whatever exact combination you want (you are not clear about how the amounts are inter-related: the obvious relationship, a2 + b2 - d2, would not give your "correct answer" for Scenario 5.). Perhaps you should be a little more precise as to the relationship, and perhaps also check your answers.

Collapse -

it looks simple...

by Kris Venugopalan In reply to looks easy

Hi John

thanks for your input. It is not that simple. Here the closing stock or the opening stock could be negative, which means back orders (pending). And the receipt in store could be more than required and that is an easy scenario. However, if the receipt is less than the required qty to supply, a simple formula cannot work. And lastly, it takes a **** a lot of time to check 5000 lines.

thanks again

cheers

Kris

Collapse -

Clarification

by john.a.wills In reply to Question about Excel form ...

What exactly do you mean by "receipt"? Amount received? And "despatch"? With the usual meanings, scenario 5 is impossible.

Collapse -

Clarification

by Kris Venugopalan In reply to Clarification

Hi John
It is a stock and sales management spread sheet which also shows back orders (that could not be supplied for want of stock) as negative. Does it make sense now

Collapse -

Clarification

by Manitobamike In reply to Question about Excel form ...

Senario 3 looks like a type should it not be negative 30.
I would suggest you need to a one more figure to the mix. Break apart outstanding and new orders

Back to Windows Forum
5 total posts (Page 1 of 1)  

Related Discussions

Related Forums