Remember Me?

#1
August 15th 18, 11:15 PM
 Junior Member First recorded activity by ExcelBanter: Apr 2013 Posts: 17
Count Weekdays and Weekends separate & < a given cell.

I have got this far,
{=COUNT(IF(WEEKDAY(A\$2:A\$32,2)<6,B\$2:B\$32))}

Col A is dates: July 29 to Aug 28
Col B is a number usually between 18 to 55

Ref Cell is average of Weekdays Col B

I want to count Number of weekdays = than Ref Cell
Same for weekends

Milo

I have got this far, Cell G2
{=COUNT(IF(WEEKDAY(A\$2:A\$32,2)<6,B\$2:B\$32))}

Col A is dates: July 29 to Aug 28
Col B is a number usually between 18 to 55

Ref Cell is average of Weekdays Col B

I want to count Number of weekdays = than Ref Cell
Same for weekends

Milo
A B C D E
1 Date Usage
2 29-Jul-18 39.69
3 30-Jul-18 43.58
4 31-Jul-18 44.90
5 1-Aug-18 47.69
6 2-Aug-18 50.69
7 3-Aug-18 42.15
8 4-Aug-18 48.08
9 5-Aug-18 51.65
10 6-Aug-18 46.63
11 7-Aug-18 46.00
12 8-Aug-18 46.00
13 9-Aug-18 50.48
14 10-Aug-18 50.11
15 11-Aug-18 51.79
16 12-Aug-18 52.00
17 13-Aug-18 56
18
19
20 Ref Cell
21 46.82
22

#2
August 15th 18, 11:19 PM
 Junior Member First recorded activity by ExcelBanter: Apr 2013 Posts: 17
File

Here is a zip of the file
Attached Files
 Average based on day of week II.zip (8.7 KB, 2 views)
#3
August 16th 18, 08:43 AM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Apr 2011 Posts: 3,632
Count Weekdays and Weekends separate & < a given cell.

Hi Milo,

Am Wed, 15 Aug 2018 23:15:39 +0100 schrieb Milo Bloom:

Ref Cell is average of Weekdays Col B

for average of weekdays try:

=SUMPRODUCT(--(WEEKDAY(A2:A17,2)<6),B2:B17)/SUMPRODUCT(N(WEEKDAY(A2:A17,2)<6))

Regards
Claus B.
--
Windows10
Office 2016
#4
August 18th 18, 06:01 PM
 Junior Member First recorded activity by ExcelBanter: Apr 2013 Posts: 17
Count Weekdays and Weekends

Ok Thanks that worked (sort of)

The sheet goes on for 32 lines header and 31 days.
the formula will error if =IF(D20="","",A19+1) is placed in Column A
Column A can be blank
Column D is Data field
If you fill in the date fields (column A) to 31 days then it averages the data by 31. It comes out ok at the end of the month.

=SUMPRODUCT(--(WEEKDAY(\$A\$2:\$A\$32,2)<6),\$D\$2:\$D\$32)/SUMPRODUCT(N(WEEKDAY(\$A\$2:\$A\$32,2)<6))

The formula works fine except if the date column contains a formula
Date Usage
29-Jul-18 39.69
30-Jul-18 43.58
31-Jul-18 44.90
1-Aug-18 47.69
2-Aug-18 50.69
3-Aug-18 42.15
4-Aug-18 48.08
5-Aug-18 51.65
6-Aug-18 46.63
7-Aug-18 46.00
8-Aug-18 46.00
9-Aug-18 50.48
10-Aug-18 50.11
11-Aug-18 51.79
12-Aug-18 52.00
13-Aug-18 48.42
14-Aug-18 42.68
15-Aug-18 48.15
16-Aug-18
#5
August 18th 18, 07:09 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Apr 2011 Posts: 3,632
Count Weekdays and Weekends separate & < a given cell.

Hi Milo,

Am Sat, 18 Aug 2018 18:01:38 +0100 schrieb Milo Bloom:

Ok Thanks that worked (sort of)

The sheet goes on for 32 lines header and 31 days.
the formula will error if =IF(D20="","",A19+1) is placed in Column
A

here it works fine with formula and with blank cells.
I guess anything is wrong with your formats.
Can you send me an example workbook to
claus_busch(at)t-online.de

Regards
Claus B.
--
Windows10
Office 2016

#6
August 18th 18, 07:38 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Apr 2011 Posts: 3,632
Count Weekdays and Weekends separate & < a given cell.

Hi Milo,

Am Sat, 18 Aug 2018 18:01:38 +0100 schrieb Milo Bloom:

The sheet goes on for 32 lines header and 31 days.
the formula will error if =IF(D20="","",A19+1) is placed in Column
A

WEEKDAY requires a date. If you have an empty string in a cell it errors
out.
But your formula is anyhow not really reliable. Beneath the empty cell
you get a wrong date.
Try in A3:
=IF(D3="";0;MAX(\$A\$2:A2)+1)
Copy the formula down and then go to Options = Advanced = Display
options for this worksheet and deactivate "Show a zero in cells that
have zero value".

Regards
Claus B.
--
Windows10
Office 2016
#7
August 20th 18, 06:17 PM
 Junior Member First recorded activity by ExcelBanter: Apr 2013 Posts: 17

Ok Thanks that worked :-)
Now one last problem, If I change the date to 5 for weekend for some reason it does not work properly. Seems again to be counting all dates, including blanks.
I did change the display options and that worked.

=SUMPRODUCT(--(WEEKDAY(A\$2:A\$32,2)5),D\$2\$32)/SUMPRODUCT(N(WEEKDAY(A\$2:A\$32,2)5))

Thanks for all the help :-)
#8
August 20th 18, 07:15 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Apr 2011 Posts: 3,632
Count Weekdays and Weekends separate & < a given cell.

Hi Milo,

Am Mon, 20 Aug 2018 18:17:29 +0100 schrieb Milo Bloom:

Now one last problem, If I change the date to 5 for weekend for some
reason it does not work properly. Seems again to be counting all dates,
including blanks.
I did change the display options and that worked.

if you have a zero in column A the weekday is Saturday and will be
counted.
Change the formula for weekends:
=SUMPRODUCT((WEEKDAY(A\$2:A\$32,2)5)*(A2:A320)*D\$2 \$32)/SUMPRODUCT((WEEKDAY(A\$2:A\$32,2)5)*(A2:A320))

Regards
Claus B.
--
Windows10
Office 2016
#9
August 20th 18, 07:18 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Apr 2011 Posts: 3,632
Count Weekdays and Weekends separate & < a given cell.

Hi again,

Am Mon, 20 Aug 2018 20:15:49 +0200 schrieb Claus Busch:

if you have a zero in column A the weekday is Saturday and will be
counted.
Change the formula for weekends:
=SUMPRODUCT((WEEKDAY(A\$2:A\$32,2)5)*(A2:A320)*D\$2 \$32)/SUMPRODUCT((WEEKDAY(A\$2:A\$32,2)5)*(A2:A320))

for the sum you don't need to check in column A is greater than 0
This will be enough:
=SUMPRODUCT((WEEKDAY(A\$2:A\$32,2)5)*D\$2\$32)/SUMPRODUCT((WEEKDAY(A\$2:A\$32,2)5)*(A2:A320))

Regards
Claus B.
--
Windows10
Office 2016
#10
August 20th 18, 09:41 PM
 Junior Member First recorded activity by ExcelBanter: Apr 2013 Posts: 17
Count Weekday and Weekend

Woo Hoo we have Victory!!

Thanks so much for the help :-)

=SUMPRODUCT((WEEKDAY(A\$2:A\$32,2)5)*(A2:A32)*D\$2:\$ D32)/SUMPRODUCT((WEEKDAY(A\$2:A\$32,2)5)*(A2:A32))

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post TLAngelo Excel Discussion (Misc queries) 0 July 10th 06 06:49 PM TLAngelo Excel Discussion (Misc queries) 7 May 12th 06 05:31 PM edwardpestian Excel Worksheet Functions 4 May 7th 06 09:13 PM [email protected] Charts and Charting in Excel 2 August 17th 05 02:09 PM hanauer Excel Discussion (Misc queries) 2 January 8th 05 03:13 AM

All times are GMT +1. The time now is 08:49 AM.