# Is this simple or complicated?

#### hannahskellam

##### New Member
Hi I am wondering if someone can help me. I have this question...
Everyday a factory makes 200 cupcakes
When the factory makes 550 cupcakes it needs to deliver them to the shop
Make a spreadsheet that lets you know when it is time to make a delivery.
*The factory cannot deliver on weekends.

I am a teacher and was going to set this as a problem to my class, however I'm not even sure I can solve it! I have used an IF statement to determine wether a delivery should go out or not. But I'm stuck because I'm not sure I can make the spreadsheet deduct 550 everytime the IF statement says its time for a delivery.

Can anybody help please? I'm starting to wonder if I have come up with an impossible task!

### Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

#### miss_ell

##### Active Member
Hello,

I've gotten it to work in the following way. First I create a table with columns A-H with the following headings and formulas:

A = Date
B = Day of the week (not needed for calculations; just to show clearly the day)
C = Units brought forward
D = Units produced
E = Cumulative total of units produced
F = Units delivered
G = Units carried forward
H = Delivery time

Formulas, starting from A1 etc, copied down:

A: (as an example, I've listed the dates from 1-31 March, all the way down to A32)
B: =A1 (formatted as dddd)
From C2 onwards: =G2
D: 200
E: =SUM(C2:D2)
F: =INT(E2/550)*550*NETWORKDAYS(A2,A2)
G: =E2-F2
H: =IF(G2>550,"Time to deliver!","")

This will result in there being no deliveries on Saturday and Sunday even when the total is over 550, and on the Monday the necessary amount will automatically be deducted from the total.

I hope this helps a bit.

Forgot to add what it should look like:

Code:
``````Date	           Day	Units B/F	Units Produced	Stock Total	Units delivered	Units carried forward	Time to deliver?
01/03/2013	Friday		200	200	0	200
02/03/2013	Saturday	200	200	400	0	400
03/03/2013	Sunday	400	200	600	0	600	Time to deliver!
04/03/2013	Monday	600	200	800	550	250
05/03/2013	Tuesday	250	200	450	0	450
06/03/2013	Wednesday	450	200	650	550	100
07/03/2013	Thursday	100	200	300	0	300
08/03/2013	Friday	300	200	500	0	500
09/03/2013	Saturday	500	200	700	0	700	Time to deliver!
10/03/2013	Sunday	700	200	900	0	900	Time to deliver!
11/03/2013	Monday	900	200	1100	1100	0``````

Last edited:

#### hannahskellam

##### New Member
Hello,

I've gotten it to work in the following way. First I create a table with columns A-H with the following headings and formulas:

[/code]

Wow this is great thank you!

Thank you very much for this!! xxxx

#### miss_ell

##### Active Member
Hello,

I'm very happy it works for you. The formula works as follows:-

=INT(E2/550) takes the total units produced divided by 550 and rounds the figure into the nearest whole number, to determine how many deliveries are needed. For example, on 04/03/2013 there are 800 units. 800 divided by 550 gives 1.45454545... so the INT function returns 1 (delivery). Therefore 1 x 550 units are delivered, with 250 remaining.

=NETWORKDAYS(A2,A2) checks the date to see if it's a weekend, when there is no delivery. For example, on 09/03/2013 there are 700 units but none are delivered because it's a Saturday. NETWORKDAYS calculates the number of weekdays between two dates, so if it's a Saturday the answer is zero, so..

700/550 x 0 = 0 units delivered.

I hope this helps.

Replies
0
Views
235
Replies
5
Views
230
Replies
2
Views
286
Replies
1
Views
167
Replies
6
Views
354

1,195,827
Messages
6,011,824
Members
441,648
Latest member
Bigjohnj

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