Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old August 15th 18, 11:15 PM
Junior Member
 
First recorded activity by ExcelBanter: Apr 2013
Posts: 18
Default 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

Thanks in Advance

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

Thanks in Advance

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   Report Post  
Old August 15th 18, 11:19 PM
Junior Member
 
First recorded activity by ExcelBanter: Apr 2013
Posts: 18
Default File

Here is a zip of the file
Attached Files
File Type: zip Average based on day of week II.zip (8.7 KB, 3 views)
  #3   Report Post  
Old August 16th 18, 08:43 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,651
Default 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   Report Post  
Old August 18th 18, 06:01 PM
Junior Member
 
First recorded activity by ExcelBanter: Apr 2013
Posts: 18
Default 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   Report Post  
Old August 18th 18, 07:09 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,651
Default 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   Report Post  
Old August 18th 18, 07:38 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,651
Default 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   Report Post  
Old August 20th 18, 06:17 PM
Junior Member
 
First recorded activity by ExcelBanter: Apr 2013
Posts: 18
Default

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   Report Post  
Old August 20th 18, 07:15 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,651
Default 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   Report Post  
Old August 20th 18, 07:18 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,651
Default 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   Report Post  
Old August 20th 18, 09:41 PM
Junior Member
 
First recorded activity by ExcelBanter: Apr 2013
Posts: 18
Smile 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))


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
WEEKENDS VS. WEEKDAYS TLAngelo Excel Discussion (Misc queries) 0 July 10th 06 06:49 PM
WEEKENDS VS. WEEKDAYS TLAngelo Excel Discussion (Misc queries) 7 May 12th 06 05:31 PM
IF statement with Weekends vs. weekdays edwardpestian Excel Worksheet Functions 4 May 7th 06 09:13 PM
chart only weekdays when data set includes weekends? [email protected] Charts and Charting in Excel 2 August 17th 05 02:09 PM
Recognizing weekends and changing to weekdays in Excel hanauer Excel Discussion (Misc queries) 2 January 8th 05 03:13 AM


All times are GMT +1. The time now is 12:07 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017