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))))
Bookmarks