Excel Formula Qn for Inventory

[Legacy 262764]

Hi,

I've been stumped trying to figure the formula for the following problem. Any help is appreciated.

I am creating a financial spreadsheet using Excel for Mac 2011 (v14.3.8) with the following rows (the columns are months of the year):-

Row 1: Pre-sales Inventory
Row 2: Total units ordered by the client
Row 3: Post sales inventory
Row 4: Number of units required
Row 5: Number of units ordered

The problematic formula relates to Row 5. Orders can only be placed in 500 unit batches. I want the system to recognize that if the row 4 cell is negative then a certain amount of stock (row 5 cell) should be ordered. This amount should be in multiples of 500.

To better explain this I've included an example...

B4=A3-B2 The number of units required to be available for February is equal to the post sales inventory for January minus the total units ordered by​

the client. If this number is negative (eg. -240) then the number of units that need to be ordered (cell A5) would be 1 * 500. If the number is -501 then 2*500 and so on. Thus making B1=A3+A5.


​

If you haven't realised, my knowledge of Excel is quite basic. Thanks for your input.

​

 

[shyy]

Could you post a screen shot?

The first mistake is you are using excel for Mac

 

[Legacy 262764]

	Jan '14	Feb '14	Mar '14	Apr '14	May '14	Jun '14	Jul '14	Aug '14	Sep '14	Oct '14	Nov '14	Dec '14
Product
Pre-sales inventory	500	429	287	74	-210	-565	-991	-1488	-2056	-2695	-3405	-4186
Total units ordered	71	142	213	284	355	426	497	568	639	710	781	852
Post-sales inventory	429	287	74	-210	-565	-991	-1488	-2056	-2695	-3405	-4186	-5038
Units required	-210	-565	-991	-1488	-2056	-2695	-3405	-4186	-5038	-5961	-6955	-8020
Stock units ordered

<tbody>
</tbody>

Here's a copy of the spreadsheet. Didn't take long for the Mac bashing! lol Just accept the inevitable!

 

[Jim885]

Assuming the spreadsheet as depicted, the formula would go into cell B7. So try this formula in the first cell for Stock Units Ordered, then drag across;
=ROUNDUP(B6/500,0.5)*-500

You should end up with this;

	A	B	C	D	E	F	G	H	I	J	K	L	M
1		Jan '14	Feb '14	Mar '14	Apr '14	May '14	Jun '14	Jul '14	Aug '14	Sep '14	Oct '14	Nov '14	Dec '14
2	Product
3	Pre-sales inventory	500	429	287	74	-210	-565	-991	-1488	-2056	-2695	-3405	-4186
4	Total units ordered	71	142	213	284	355	426	497	568	639	710	781	852
5	Post-sales inventory	429	287	74	-210	-565	-991	-1488	-2056	-2695	-3405	-4186	-5038
6	Units required	-210	-565	-991	-1488	-2056	-2695	-3405	-4186	-5038	-5961	-6955	-8020
7	Stock units ordered	500	1000	1000	1500	2500	3000	3500	4500	5500	6000	7000	8500

<colgroup><col style="width: 24pt; mso-width-source: userset; mso-width-alt: 1170;" width="32"> <col style="width: 119pt; mso-width-source: userset; mso-width-alt: 5778;" width="158"> <col style="width: 49pt; mso-width-source: userset; mso-width-alt: 2377;" width="65"> <col style="width: 51pt; mso-width-source: userset; mso-width-alt: 2486;" width="68"> <col style="width: 52pt; mso-width-source: userset; mso-width-alt: 2523;" width="69"> <col style="width: 50pt; mso-width-source: userset; mso-width-alt: 2450;" width="67"> <col style="width: 53pt; mso-width-source: userset; mso-width-alt: 2596;" width="71"> <col style="width: 50pt; mso-width-source: userset; mso-width-alt: 2413;" width="66"> <col style="width: 47pt; mso-width-source: userset; mso-width-alt: 2267;" width="62"> <col style="width: 52pt; mso-width-source: userset; mso-width-alt: 2523;" width="69"> <col style="width: 51pt; mso-width-source: userset; mso-width-alt: 2486;" width="68"> <col style="width: 50pt; mso-width-source: userset; mso-width-alt: 2413;" width="66"> <col style="width: 53pt; mso-width-source: userset; mso-width-alt: 2560;" width="70"> <col style="width: 51pt; mso-width-source: userset; mso-width-alt: 2486;" width="68"> <tbody>
</tbody>

 

[Jim885]

Does that work for you?

 

[Legacy 262764]

Why does it look so simple once it's done?! Thanks Jim885!

 

[Jim885]

Why does it look so simple once it's done?! Thanks Jim885!

I wish I knew the answer to that!

Glad to help.