Subtracting until zero then taking remainder from another cell.

fujisan

New Member
Joined
Oct 7, 2017
Messages
1
Hi all,

I am having a problem thinking of formula to calculate my wine stock with Excel 2013.

Here is an example:

Wine NameOpening StockBottlesTotalSalesClosing Stock
GlassBottleTotalReceivedGlassBottletotalGlassBottletotal
Wine 10.01.01.00.01.00.40.00.60.00.6
Wine 20.41.01.40.01.40.80.00.80.61.00.6

<tbody>
</tbody>


The problem comes from glasses and bottles being separate. 1 bottle = 5 glasses or 0.2. If we sell more glasses than we have in opening stock then we have to open a new bottle. I want closing stock to minus glass sales from opening glasses until it is zero, then take the remainder from a freshly opened bottle (worth 5 glasses); decreasing the closing bottle count when doing so and adding the remainder to glasses.

I hope this can be done its been driving me mad trying to figure this out and takes ages inputting these manually every time!

Thank you very much in advance
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
43,684
Office Version
365
Platform
Windows
Welcome to the MrExcel board!

Your second example doesn't make sense to me. Opening stock was 1.4, no purchases give a total still of 1.4. If glass sales were 0.8 how could there still be 1 full bottle left?

Anyway, see if these help.

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: center;;">Op Stock</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">Bottles</td><td style="text-align: center;;">Total</td><td style="text-align: center;;">Sales</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">Cl Stock</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: center;;">Glass</td><td style="text-align: center;;">Bottle</td><td style="text-align: center;;">Total</td><td style="text-align: center;;">Received</td><td style="text-align: center;;"></td><td style="text-align: center;;">Glass</td><td style="text-align: center;;">Bottle</td><td style="text-align: center;;"></td><td style="text-align: center;;">Glass</td><td style="text-align: center;;">Bottle</td><td style="text-align: center;;">total</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: center;;">0</td><td style="text-align: center;;">1</td><td style="text-align: center;;">1</td><td style="text-align: center;;">0</td><td style="text-align: center;;">1</td><td style="text-align: center;;">0.4</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">0.6</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0.6</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: center;;">0.4</td><td style="text-align: center;;">1</td><td style="text-align: center;;">1.4</td><td style="text-align: center;;">0</td><td style="text-align: center;;">1.4</td><td style="text-align: center;;">0.8</td><td style="text-align: center;;">0</td><td style="text-align: center;;"></td><td style="text-align: center;;">0.6</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0.6</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: center;;">0.4</td><td style="text-align: center;;">1</td><td style="text-align: center;;">1.4</td><td style="text-align: center;;">3</td><td style="text-align: center;;">4.4</td><td style="text-align: center;;">1.6</td><td style="text-align: center;;">1</td><td style="text-align: center;;"></td><td style="text-align: center;;">0.8</td><td style="text-align: center;;">1</td><td style="text-align: center;;">1.8</td></tr></tbody></table><p style="width:8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Wine stock</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">J3</th><td style="text-align:left">=F3-H3-G3-K3</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">K3</th><td style="text-align:left">=INT(<font color="Blue">F3-H3-G3</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">L3</th><td style="text-align:left">=SUM(<font color="Blue">J3:K3</font>)</td></tr></tbody></table></td></tr></table><br />
 

Watch MrExcel Video

Forum statistics

Threads
1,090,249
Messages
5,413,300
Members
403,472
Latest member
ArtisticOwl

This Week's Hot Topics

Top