# Formula/Logic Writing

#### ncrb_10

##### New Member
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>

### Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
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``

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.

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?

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?

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.

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.

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?

 Jan-19 Feb-19 Mar-19 Apr-19 May-19 Jun-19 Jul-19 Aug-19 Sep-19 Oct-19 Nov-19 Dec-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>

FYI - in the Excel sheet "Jan-19" is Row 10 Column G

Replies
15
Views
491
Replies
7
Views
213
Replies
2
Views
115
Replies
3
Views
228
Replies
1
Views
148

1,203,524
Messages
6,055,904
Members
444,832
Latest member
bgunnett8

### 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.

### Which adblocker are you using?

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

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