Ignore data in weekend column

nparsons75

Well-known Member
Joined
Sep 23, 2013
Messages
1,159
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
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
44,640
Office Version
365
Platform
Windows
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

Well-known Member
Joined
Sep 23, 2013
Messages
1,159
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
44,640
Office Version
365
Platform
Windows
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
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJ
112345678910111213141516171819202122232425262728293031Average 5 DaysExpected @ 5 Days DaysAverage 7 DaysExpected @ 7 Days Days
2Red220012812008324700221
3.133333333
21.93333333
2.238095238
22.38095238
3Green8973193383591426887965.66666666739.666666675.66666666756.66666667
4Blue5889555476368686385776.13333333342.933333336.14285714361.42857143
5Yellow2000000000000000000040.42.80.2857142862.857142857
Production
 

nparsons75

Well-known Member
Joined
Sep 23, 2013
Messages
1,159
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
44,640
Office Version
365
Platform
Windows
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. :)
 
Last edited:

nparsons75

Well-known Member
Joined
Sep 23, 2013
Messages
1,159
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

Well-known Member
Joined
Sep 23, 2013
Messages
1,159
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
44,640
Office Version
365
Platform
Windows
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
ABCDEFGHACADAEAF
1Date
1/3/18
2/3/183/3/184/3/185/3/186/3/187/3/1828/3/1829/3/1830/3/1831/3/18
2Day Off
FALSE
FALSETRUETRUEFALSETRUEFALSEFALSEFALSETRUETRUE
3
4
5UK Holidays
61/01/2018
712/02/2018
86/03/2018
930/03/2018
1015/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
ABCDEFGHKLVWAEAFAGAHAIAJ
11/03/18
2/03/18
3/03/184/03/185/03/186/03/187/03/1810/03/1811/03/1821/03/1822/03/1830/03/1831/03/18Av. (Work days)Exp. (Work Days)Av. (7 Days)Exp. (7 Days)
2Red2200128001
3.214285714
19.28571429
2.238095238
22.38095238
3Green89731933565.42857142932.571428575.66666666756.66666667
4Blue58895556276.21428571437.285714296.09523809560.95238095
5Yellow20000000040.4285714292.5714285710.2857142862.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>
 

Watch MrExcel Video

Forum statistics

Threads
1,099,080
Messages
5,466,495
Members
406,484
Latest member
kaksolver

This Week's Hot Topics

Top