Inventory Formula

R

RAMU

Active Member
Joined
Dec 11, 2009
Messages
345
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
  2. Mobile
Dear All,

I have a workbook with two sheets:

Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 11pt; FONT-FAMILY: Calibri,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 68px"><COL style="WIDTH: 185px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 243px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: right">31-Jul-11</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD> </TD><TD> Daily Demand</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right">944.5</TD><TD> </TD><TD style="TEXT-ALIGN: left"> Demand During Lead Time (DDL)</TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD> </TD><TD> Transport Lead Time</TD><TD style="TEXT-ALIGN: right">6</TD><TD> </TD><TD style="TEXT-ALIGN: left"> Number of Drums Necessary for DDL</TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD> </TD><TD> Production Lead Time</TD><TD style="TEXT-ALIGN: right">2</TD><TD> </TD><TD style="TEXT-ALIGN: left"> Safety Margin</TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: left"> Number of Extra Drums for Safety</TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: left"> Total Quantity of Material</TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: left"> Days of Demand Coverage</TD><TD> </TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>A1</TD><TD>=TODAY()</TD></TR><TR><TD>C2</TD><TD>{=AVERAGE(INDEX(Sheet2!B:B,LARGE(IF(Sheet2!B:B<>"",ROW(Sheet2!B:B),""),10)):INDEX(Sheet2!B:B,MATCH(1E+307,Sheet2!B:B)))}</TD></TR></TBODY></TABLE></TD></TR><TR><TD>Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!</TD></TR></TBODY></TABLE>



Sheet2

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 11pt; FONT-FAMILY: Calibri,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: right">1-Jul-11</TD><TD style="TEXT-ALIGN: right">900</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: right">2-Jul-11</TD><TD style="TEXT-ALIGN: right">1050</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: right">3-Jul-11</TD><TD style="TEXT-ALIGN: right">880</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: right">4-Jul-11</TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: right">5-Jul-11</TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: right">6-Jul-11</TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: right">7-Jul-11</TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD style="TEXT-ALIGN: right">8-Jul-11</TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: right">9-Jul-11</TD><TD style="TEXT-ALIGN: right">880</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD style="TEXT-ALIGN: right">10-Jul-11</TD><TD style="TEXT-ALIGN: right">1100</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD style="TEXT-ALIGN: right">11-Jul-11</TD><TD style="TEXT-ALIGN: right">980</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD style="TEXT-ALIGN: right">12-Jul-11</TD><TD style="TEXT-ALIGN: right">850</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">14</TD><TD style="TEXT-ALIGN: right">13-Jul-11</TD><TD style="TEXT-ALIGN: right">770</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">15</TD><TD style="TEXT-ALIGN: right">14-Jul-11</TD><TD style="TEXT-ALIGN: right">970</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">16</TD><TD style="TEXT-ALIGN: right">15-Jul-11</TD><TD style="TEXT-ALIGN: right">1050</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">17</TD><TD style="TEXT-ALIGN: right">16-Jul-11</TD><TD style="TEXT-ALIGN: right">1120</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">18</TD><TD style="TEXT-ALIGN: right">17-Jul-11</TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">19</TD><TD style="TEXT-ALIGN: right">18-Jul-11</TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">20</TD><TD style="TEXT-ALIGN: right">19-Jul-11</TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">21</TD><TD style="TEXT-ALIGN: right">20-Jul-11</TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">22</TD><TD style="TEXT-ALIGN: right">21-Jul-11</TD><TD style="TEXT-ALIGN: right">889</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">23</TD><TD style="TEXT-ALIGN: right">22-Jul-11</TD><TD style="TEXT-ALIGN: right">880</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">24</TD><TD style="TEXT-ALIGN: right">23-Jul-11</TD><TD style="TEXT-ALIGN: right">854</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">25</TD><TD style="TEXT-ALIGN: right">24-Jul-11</TD><TD style="TEXT-ALIGN: right">845</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">26</TD><TD style="TEXT-ALIGN: right">25-Jul-11</TD><TD style="TEXT-ALIGN: right">987</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">27</TD><TD style="TEXT-ALIGN: right">26-Jul-11</TD><TD style="TEXT-ALIGN: right">860</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">28</TD><TD style="TEXT-ALIGN: right">27-Jul-11</TD><TD style="TEXT-ALIGN: right">990</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">29</TD><TD style="TEXT-ALIGN: right">28-Jul-11</TD><TD style="TEXT-ALIGN: right">970</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">30</TD><TD style="TEXT-ALIGN: right">29-Jul-11</TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">31</TD><TD style="TEXT-ALIGN: right">30-Jul-11</TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">32</TD><TD style="TEXT-ALIGN: right">31-Jul-11</TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">33</TD><TD style="TEXT-ALIGN: right">1-Aug-11</TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">34</TD><TD style="TEXT-ALIGN: right">2-Aug-11</TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">35</TD><TD style="TEXT-ALIGN: right">3-Aug-11</TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">36</TD><TD style="TEXT-ALIGN: right">4-Aug-11</TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">37</TD><TD style="TEXT-ALIGN: right">5-Aug-11</TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">38</TD><TD style="TEXT-ALIGN: right">6-Aug-11</TD><TD> </TD></TR></TBODY></TABLE>


In Sheet 1 C2 I want to get the average consumption of last 10 or 15 entries. I have already used a formula but I think I had done that like an idiot. That formula is giving me correct figure if I put the cells blanks in sheet 2 column B but if I enter 0 instead of blank it is not giving me the same result. I want that the result in sheet1 C2 will be calculated based on last 10 or 15 since today [=TODAY()] but it must be same whether cells of sheet2 Columns B remains blank or 0.

If possible also provide me a formula for Safety Stock (Safety margin) & Reorder Level.

Please help.
thanks in advance.

RAMU
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Sort by date Sort by votes
Dear Friends,

Little mistake the calculation would be started from yesterday's entry.
I mean suppose today is 31st July, then calculation will be counted from 30th July 2011 & will take last 10 or 15 entries.

Pls help
Thanks & Regards

RAMU
 
Upvote 0
Dear All,

Is it not possible ? Kindly help me. I have tried lot throughout the day but yet to solve the problem.

Regards
RAMU
 
Upvote 0
Dear Friends,

Should I take it that it is not possible in excel !!!!!!
I think so, that average result must be different if a cell contains 0 & the same cell remains blank....

Regards
RAMU
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
623
smozgur
smozgur
E
  • Question Question
VBA code clean up
Replies
9
Views
583
mumps
mumps
E
  • Question Question
Modify system Report
Replies
3
Views
84
Skyybot
Skyybot
F
  • Question Question
VBA Macro Issue: Replacing Simple Formula with Complex Formula Causes Error
Replies
2
Views
107
flashgordie
F
D
  • Question Question
Looking for a way to allocate shifts between teams, maybe a LET formula
Replies
0
Views
90
Dazzybeeguy
D

Forum statistics

Threads
1,224,604
Messages
6,179,857
Members
452,948
Latest member
UsmanAli786

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top