Taking Action Based on Day of The Week

HyCohen

New Member
Joined
Oct 7, 2013
Messages
1
Hello,

Let's say I am selling a weekly newspaper @ $1 each (retail), and I purchase 10 newspapers at the wholesale price of $0.25 each. During the 7 day week — Wednesday through Tuesday — I sold 8 newspapers, leaving me with 2 leftover papers from that week. So, when the new newspaper comes out on Wednesday I will purchase a new batch of papers to sell. However, I also want to sell the remaining 2 newspapers from the previous week at a discount.

Another example, but as a spreadsheet:

Date
Current Week's Papers Purchased
No. of Current Week's Papers Sold
No. of Previous Week's Papers Sold
Total Current Week's Papers In Stock
Total Previous Week's Papers In Stock
Notes
10/2
10
1
1
9
4
To start, I had 5 prev. week's papers left over.
10/3
2
0
7
4
10/4
1
2
6
2
10/5
4
0
2
2
10/6
4
1
0
5
2
Since I was down to 2 cur. week's paper's left I got 4 more = 6, but I sold 1 = 4 in stock
10/7
0
2
1
3
1
10/8
0
0
0
3
1
10/9
10
1
0
9
3
New week: purchased 10 current week's papers & sold 1 = 9 left in stock. Moved the 3 papers remaining in the current week's papers column into the prev. week's newspaper column, and lost the 1 paper that was in the prev. week's paper column.

<tbody>
</tbody>


How can I get Excel to move the papers that are in the current week's papers column into the past week's papers column where they will be tracked and sold at a reduced rate?

Thanks for your help!

Warmly,
Hy with Guide Dog, "Fleetwood"
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Redwolfx

Well-known Member
Joined
Feb 22, 2013
Messages
1,161
Just Checking your "Weeks" Go Wednesday to Tuesday correct?

If so...

After Entering initial data in Row 2, Assuming the Date is in Column A, Current Week in Column E and Previous week in F.

in Cell E3 =IF(TEXT(A3,"ddd")="Wed",B3-C3,E2+B3-C3)
in Cell F3 =IF(TEXT(A3,"ddd")="Wed",E2-D3,F2-D3)
 

Watch MrExcel Video

Forum statistics

Threads
1,108,755
Messages
5,524,670
Members
409,597
Latest member
Dannydev

This Week's Hot Topics

Top