Formula to average work days and non work days (weekends)

[pauldaddyadams]

Hi all

I need some help please to work out an average based on either a work day or weekend.

e.g. in Cells C4:Z4 I have amounts charged per day. What I would like in column AC is to work out the average in that row of all yellow cells. Then in column AD to work out the average of all green cells.

Can anyone help with the formula please?

[alansidman]

In Power Query, I have extracted the desired info. It is in a different format than you have requested, but you can re-arrange as needed.

Excel 2016 (Windows) 32 bit

	AC	AD	AE
2	Column1	Custom	Average
3	E.007115.07.01	Weekday	6643.099375
4	E.007115.07.01	Weekend	5227.97375
5	E.007116.07.01	Weekday	15554.13438
6	E.007116.07.01	Weekend	14451.625
7	E.007116.05.01	Weekday	650.28
8	E.007116.05.01	Weekend	68.78
9	E.007116.08.03.01	Weekday	218.0533333
10	E.007160.05.01.01	Weekday	2201.258438
11	E.007160.05.01.01	Weekend	374.7378125
12	E.007160.05.01.02	Weekday	177.87
13	E.007160.05.01.02	Weekend	168.84
14	E.007077.05.01.01	Weekday	1318.317031
15	E.007077.05.01.01	Weekend	84.84

Sheet: Data

Please Login or Register  to view this content.

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

[Fluff13]

Or with a formula

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

[CARACALLA]

AC4=IFERROR(SUMPRODUCT((WEEKDAY($C$3:$Z$3,2)<=5)*($B$4:$B$10=$B4)*($C$4:$Z$10>0)*($C$4:$Z$10))/SUMPRODUCT((WEEKDAY($C$3:$Z$3,2)<=5)*($C4:$Z4<>"")),"")

Copy down


AD4=IFERROR(SUMPRODUCT((WEEKDAY($C$3:$Z$3,2)>=6)*($B$4:$B$10=$B4)*($C$4:$Z$10>0)*($C$4:$Z$10))/SUMPRODUCT((WEEKDAY($C$3:$Z$3,2)>=6)*($C4:$Z4<>"")),"")

Copy down

[pauldaddyadams]

Thanks Alan - sadly I cant use that functionality. I tried but I am unable to install the Add in so I will be contacting IT to enable that on my work laptop.

Thank you Fluff13 and Caracalla, both worked great!

[Fluff13]

Glad to help & thanks for the feedback.

[alansidman]

@pauldaddyadams
Power Query is already part of your version. If you read my entire post, you would have seen that it is called Get and Transform and further it is found on the Data Tab of the ribbon.

There is no need to download any thing. If you follow the directions, I posted, and look at the file provided, you will find everything you need to make it work.