+ Reply to Thread
Results 1 to 4 of 4

Using weekday function to get average if criteria range equals weekday

  1. #1
    Registered User
    Join Date
    10-03-2020
    Location
    Broomall, Pennsylvania
    MS-Off Ver
    Microsoft 365
    Posts
    7

    Using weekday function to get average if criteria range equals weekday

    Hello,
    I am trying to get the monthly average of a range of data based on the following criteria: Utility = "BGE", Date isn't a holiday, and date is a weekday. I have gotten the average for the range based on all the criteria except for the weekday condition. I have included the formula I used to include only the weekdays below and attached the spreadsheet as well. I would have though creating another condition in the if function setting weekday = Monday through Friday would have worked, but i receive a "N/A" error.

    =AVERAGE(IF(MATCH('Portfolio 1'!$AD$28:$AD$33,'Portfolio 1'!$C$28:$C$759)*(weekday($C$28:$C$759)={2,3,4,5,6}),AVERAGE(IF(('Portfolio 1'!$B$28:$B$759=Sheet6!A$9)*('Portfolio 1'!$C$28:$C$759>=Sheet6!$C10)*('Portfolio 1'!$C$28:$C$759<=EOMONTH(Sheet6!$C10,0)),'Portfolio 1'!$K$28:$Z$759))))
    Attached Files Attached Files

  2. #2
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,213

    Re: Using weekday function to get average if criteria range equals weekday

    Please try

    =AVERAGE(IF((MONTH('Portfolio 1'!$C$4:$C$735)=MONTH(C9))*NETWORKDAYS(+'Portfolio 1'!$C$4:$C$735,+'Portfolio 1'!$C$4:$C$735,'Portfolio 1'!$AD$4:$AD$19)*('Portfolio 1'!$B$4:$B$735=$A$9),'Portfolio 1'!$D$4:$AA$735))
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    10-03-2020
    Location
    Broomall, Pennsylvania
    MS-Off Ver
    Microsoft 365
    Posts
    7

    Re: Using weekday function to get average if criteria range equals weekday

    Thank you Bo_Ry, that worked. What do the +'s in front of Portfolio do? I haven't seen someone use that before.

  4. #4
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,213

    Re: Using weekday function to get average if criteria range equals weekday

    Some functions eg; NETWORKDAYS() , Weeknum(), N() can not deal with array.

    + sign help to do array calcalution.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Weekday IF Function add in a Holiday Range
    By Alphabex in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-11-2018, 06:51 PM
  2. [SOLVED] Dosn't work if I use range of address (AVERAGE, IF, WEEKDAY)
    By hbartolin in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 08-26-2016, 07:27 AM
  3. Criteria for query won't accept weekday function with OR statement
    By DanNGP in forum Access Tables & Databases
    Replies: 6
    Last Post: 01-25-2016, 05:41 PM
  4. Excel VBA code to return weekday name from a date then autofilters for userdefined weekday
    By studyengineering in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-10-2016, 03:24 PM
  5. [SOLVED] Calculate weekday end date based on sum of weekday start date and cell value
    By Rerock in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-01-2014, 09:17 AM
  6. [SOLVED] If Weekday() = vbSaturday Or Weekday() = vbSunday Then
    By Excelnoub in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-09-2014, 09:48 AM
  7. Average If Weekday
    By freud1 in forum Excel General
    Replies: 3
    Last Post: 07-08-2011, 05:09 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1