Ignore data in weekend column

[nparsons75]

Hi, I have a number of columns and rows of data. The columns consist of dates from the 1st to the 31st of the month.

The rows contain various items, for example,

RED
GREEN
BLUE
YELLOW

For each colour there will be a quantity. Basically for each day of the week we will make a number of each colour.

Sometimes we will make on a weekend, but mainly during a weekday.

My columns are coloured using conditional formatting to highlight the weekend days.

At the end of the columns, I need to calculate, on average, how many of each colour have we produced, however I need to not include anything produced on a weekend. I want to work out, based on the number of weekdays left in the month and the average of colours produced so far on weekdays, how many do i estimate will be made in the remaining weekdays.

Hope that makes sens

Thank you very much in advance for any help

 

[Peter_SSs]

At the end of the columns, I need to calculate, on average, how many of each colour have we produced, ....

1. Is that calculation always from the 1st of the month up to and including 'yesterday', or something like that? If not, how do we work out how many columns we need to average?

2. Do all rows have the same number of columns completed?

3. If no REDs are produced on a particular day (including a weekend day), does that cell contain zero or is it blank?

 

[nparsons75]

Hi, thanks for the reply.

1. Yes, the calculation is always from the 1st of the month. and including yesterday.

2. No, sometimes a column may not have data, so it will either be zero or blank. If the rule needs to be either one then I can set that.

3. Again, either zero or blank, depending on the rule I set.

Im trying to work out, based on the available days left in the month, how many REDS we will produce in the month (based on the current average output using weekdays). If this value comes in less than out target, then the weekends may need to operate. I therefore would also like to estimate a second value, how many REDS would be produced if we were to operate including weekdays and weekends. These values will change daily based on the average average qty.

Hope that makes sense....difficult to explain

 

[Peter_SSs]

OK, so suppose every day, that column is filled in, including 0 if nothing is produced. See if this does what you want.

My row 1 below is actually dates: 1 March 2018, 2 March 2018 etc. but I have formatted them to show day only to make my screen shot a bit smaller in the forum. Weekends highlighted.
Each formula is copied down.

Col AG is the average produced on weekdays.
Col AH is how many would be produced in the remaining weekdays at the average from col AG.
Col AI is the average produced, including weekends.
Col AJ is how many would be produced in the remaining days, including weekends at the average from col AI.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ
1		1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31	Average 5 Days	Expected @ 5 Days Days	Average 7 Days	Expected @ 7 Days Days
2	Red	2	2	0	0	1	2	8	1	2	0	0	8	3	2	4	7	0	0	2	2	1											3.133333333	21.93333333	2.238095238	22.38095238
3	Green	8	9	7	3	1	9	3	3	8	3	5	9	1	4	2	6	8	8	7	9	6											5.666666667	39.66666667	5.666666667	56.66666667
4	Blue	5	8	8	9	5	5	5	4	7	6	3	6	8	6	8	6	3	8	5	7	7											6.133333333	42.93333333	6.142857143	61.42857143
5	Yellow	2	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	4											0.4	2.8	0.285714286	2.857142857
Production

 

[nparsons75]

Brilliant....

A couple of questions...

AH, is this the expected total quantity for the month, or the additional quantity over and above what has already been produced?

Same for AJ

Thank you

 

[Peter_SSs]

AH, is this the expected total quantity for the month, or the additional quantity over and above what has already been produced?

Same for AJ

In each case it is the additional quantity over and above what has already been produced.
For example, if you look at Red in my example and sum all the values shown for the first 21 days you get 47 produced already so neither AH nor AJ could possibly be the total for the whole month.

 

[nparsons75]

Thank you, fully understood. Works great.

You highlight the weekends in yellow, how can you highlight the column of data also underneath and also include uk holidays, or should that be a different post?

 

[nparsons75]

I have just thought of something. How could I include Holidays within the dates. For example, although there are 21 working days in March, one of these days is a Holiday (30th March). Therefore less days to produce?

 

[Peter_SSs]

If I have understood correctly, here is one way to deal with all that.

1. Introduce a new sheet. I've called mine 'Hols & Weekends'. (As with my previous screen shot, my main sheet is called 'Production' so you may need to alter the formula to reflect your sheet name)

2. Heading for your UK Holidays in A5 & list the holidays below that.

3. Turn A5:A.. into a formal table (Insert -> Table) and via the Name Manager on the Formulas ribbon tab rename the table as UKHols.

4. Copy the formulas in B1:B2 across to column AF, allowing for up to 31 days in a month.

5. Select B2:AF2 and name that range as 'DaysOff'

Notes
Row 1 is really just for visual reference & is not used in my solution.
Row 2 should identify both weekends and any date in the holiday table that falls in the relevant month.
I have hidden most columns just for this screen shot but you can see that March 6 and 30 are normal weekdays but identified as days off because of the UKHols table.
Dates can be added/removed from the UKHols table and the updated info should be reflected in row 2.

Excel Workbook
	A	B	C	D	E	F	G	H	AC	AD	AE	AF
1	Date	1/3/18	2/3/18	3/3/18	4/3/18	5/3/18	6/3/18	7/3/18	28/3/18	29/3/18	30/3/18	31/3/18
2	Day Off	FALSE	FALSE	TRUE	TRUE	FALSE	TRUE	FALSE	FALSE	FALSE	TRUE	TRUE
3
4
5	UK Holidays
6	1/01/2018
7	12/02/2018
8	6/03/2018
9	30/03/2018
10	15/05/2018
11
Hols & Weekends

6. On the main sheet ('Production' for me) ..
- Enter the first date of the month in B1.
- Enter the C1 formula and copy across to AF1.
- Select B1:AF?? and apply the Conditional Formatting shown.
- Note that I have provided some different formulas in columns AG:AJ.
- When you change to a new month, you should simply have to change the date in cell B1 on this sheet.
- Again I have hidden a lot of columns on this sheet for my screen shot.

Post back with any problems of if you need more detailed instructions for any of the steps.

Excel Workbook
	A	B	C	D	E	F	G	H	K	L	V	W	AE	AF	AG	AH	AI	AJ
1		1/03/18	2/03/18	3/03/18	4/03/18	5/03/18	6/03/18	7/03/18	10/03/18	11/03/18	21/03/18	22/03/18	30/03/18	31/03/18	Av. (Work days)	Exp. (Work Days)	Av. (7 Days)	Exp. (7 Days)
2	Red	2	2	0	0	1	2	8	0	0	1				3.214285714	19.28571429	2.238095238	22.38095238
3	Green	8	9	7	3	1	9	3	3	5	6				5.428571429	32.57142857	5.666666667	56.66666667
4	Blue	5	8	8	9	5	5	5	6	2	7				6.214285714	37.28571429	6.095238095	60.95238095
5	Yellow	2	0	0	0	0	0	0	0	0	4				0.428571429	2.571428571	0.285714286	2.857142857
Production

#VALUE!

</td></tr></table></td></tr></table> <table style="font-family:Arial; font-size:10pt; background-color:#fffcf9; border-style: groove ;border-color:#ff0000"><tr><td ><b>Conditional formatting </b></td></tr><tr><td ><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial; font-size:10pt; padding-left:2pt; padding-right:2pt; "><tr><td >Cell</td><td >Nr.: / Condition</td><td >Format</td></tr><tr><td >B1</td><td >1. / Formula is =INDEX(DaysOff,COLUMNS($B1:B1))</td><td style="background-color:#ffff00; ">Abc</td></tr><tr><td >C1</td><td >1. / Formula is =INDEX(DaysOff,COLUMNS($B1:B1))</td><td style="background-color:#ffff00; ">Abc</td></tr></table></td></tr><tr><td ><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#ff0000;background-color:#fffcf9; color:#000000; "><tr><td ><b>Names in Formulas </b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Name</td><td >Applies to</td></tr><tr><td >B1</td><td >DaysOff</td><td >='Hols & Weekends'!$B$2:$AF$2</td></tr><tr><td >C1</td><td >DaysOff</td><td >='Hols & Weekends'!$B$2:$AF$2</td></tr></table></td></tr></table></td></tr></table>