Formula/Logic Writing

ncrb_10

New Member
Joined
Feb 14, 2019
Messages
23
Hello,

I am building an inventory production schedule and need help writing a formula for the following logic.

For example, in Row A, Column A:L (January-December), I have projected sales of 25 units per month (starting in June). Additionally, my minimum monthly production order is for 30 units (Row B).

Therefore, my ending inventory each month is 5 units. At the end of 5 months, I should have accumulated 25 units in inventory and not have to produce any units on the 6th month.

How would I write a formula that displays the following without having to do any manual entry. My main issue is overcoming circular references. Thanks in advance for the help!

Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec
Sales
0
0
0
0
0
25
25
25
25
25
25
25
Production
0
0
0
0
0
30
30
30
30
30
0
30
Ending Inv.
0
0
0
0
0
5
10
15
20
25
0
5

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

CyrusTheVirus

Well-known Member
Joined
Jan 28, 2015
Messages
737
Office Version
  1. 365
Platform
  1. Windows
welcome to the forums. you could try the below formulas, please test them because not entirely sure what you're trying to achieve, given that your example looks unusually straightforward, so not sure the formulas will account for any variations that may arise with your data. reach out with questions.

F5 (copied over):
Code:
=25
F3 (copied over):
Code:
=IF(E4-F2<0,30,0)
F4 (copied over):
Code:
=E4+F3-F2
 

ncrb_10

New Member
Joined
Feb 14, 2019
Messages
23
Thank you for your reply! Can you please clarify what F5, F4, and F3 are referring to? I would assume column and row, but I do not have 5 rows. Maybe I am misunderstanding.
 

CyrusTheVirus

Well-known Member
Joined
Jan 28, 2015
Messages
737
Office Version
  1. 365
Platform
  1. Windows
well, actually F5 meant F2, but you don't even have to type that formula, but the other ones (F3 and F4) you do... does that help clarify?
 

ncrb_10

New Member
Joined
Feb 14, 2019
Messages
23

ADVERTISEMENT

That helped tremendously! I guess sometimes you stare at a screen too long and start confusing yourself. However, I just recognized that solved half of my problem.

I actually need to produce inventory produce inventory for the following month. In other words, "30" should start at "E3" in order to satisfy sales in F25. Based on the formula you provided, "E3" would be 0. How can the formula be modified to accommodate?

Thanks again for your help.
 

ncrb_10

New Member
Joined
Feb 14, 2019
Messages
23
The issue I seem to be having is that if I start the formula in F3 one column back, I run into a circular reference.
 

CyrusTheVirus

Well-known Member
Joined
Jan 28, 2015
Messages
737
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

who's formula? yours or mine? can you please copy/paste a sample of your new data set so i can see and please list all formulas being used.
 

ncrb_10

New Member
Joined
Feb 14, 2019
Messages
23
m0LOXdabfU5pzPo9zBjgDnAFRM6BIwKErwygj756ZNlTKZNN3ISQJew19fQM0hlFTvahFwMFxBjgDnAHOAGeAMzB2BvzMZvMwbbt0eNyGh4YwxLZg0vVoyJZOaUyHG2J5dLE8dmfn1w0PUTmqT4lCG0I65d 8eQtBdMQBD5wBzgBngDPAGeAMcAY4A5wBzgBngDPwbBj4PxMrweDdJr6FAAAAAElFTkSuQmCC


Row 11 is pulling sales data from a profit and loss statement. Currently, Row 12 Column L is running the formula you suggested =if(k13-l11<0,$C$9,0)

In this table "$C$9" is a cell that contains the minimum units I have to produce per month.

Row 13 Column L is =K12-L11+L12 or previous months production, minus current months sales, plus current months production.

My goal is the following logic: if L11>0, then K12=34. However, if K13>L11, then K12=0. My issue is that since K13 relies on a formula referencing K12, I end up with a circular reference. The formula you provided me with works perfect, but it tells me that I need to produce during the current month. I need it to tell me to produce one month prior. Does that make more sense?
 

ncrb_10

New Member
Joined
Feb 14, 2019
Messages
23
Jan-19Feb-19Mar-19Apr-19May-19Jun-19Jul-19Aug-19Sep-19Oct-19Nov-19Dec-19
Total Barrels Sold - - - - - 21 21 21 21 21 21 21
Production Batch - - - - - 34 34 - 34 34 - 34
Ending Inventory - - - - 13 47 13 13 47 13 13

<colgroup><col style="mso-width-source:userset;mso-width-alt:469;width:11pt" width="15"> <col style="mso-width-source:userset;mso-width-alt:6186;width:145pt" width="193"> <col style="mso-width-source:userset;mso-width-alt:3413;width:80pt" width="107"> <col style="mso-width-source:userset;mso-width-alt:2090;width:49pt" width="65"> <col style="mso-width-source:userset;mso-width-alt:3200;width:75pt" width="100"> <col style="mso-width-source:userset;mso-width-alt:725;width:17pt" width="23"> <col style="mso-width-source:userset;mso-width-alt:1664;width:39pt" width="52"> <col style="mso-width-source:userset;mso-width-alt:1706;width:40pt" width="53"> <col style="mso-width-source:userset;mso-width-alt:1834;width:43pt" width="57"> <col style="mso-width-source:userset;mso-width-alt:1706;width:40pt" width="53"> <col style="mso-width-source:userset;mso-width-alt:1877;width:44pt" width="59"> <col style="mso-width-source:userset;mso-width-alt:1664;width:39pt" width="52"> <col style="mso-width-source:userset;mso-width-alt:1536;width:36pt" width="48"> <col style="mso-width-source:userset;mso-width-alt:1792;width:42pt" width="56"> <col style="mso-width-source:userset;mso-width-alt:1706; width:40pt" width="53" span="2"> <col style="mso-width-source:userset;mso-width-alt:1792;width:42pt" width="56"> <col style="mso-width-source:userset;mso-width-alt:1749;width:41pt" width="55"> </colgroup><tbody>
</tbody>
<style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 12pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri, sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl63 { }.xl64 { color: white; font-weight: 700; text-align: center; background: rgb(31, 78, 120) none repeat scroll 0% 0%; }.xl65 { }.xl66 { text-align: right; }</style>
 

Watch MrExcel Video

Forum statistics

Threads
1,122,910
Messages
5,598,803
Members
414,260
Latest member
joishe

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
Top