advance formula needed for avg

Tonyk1051

Board Regular
Joined
Feb 1, 2021
Messages
132
Office Version
  1. 2019
Platform
  1. Windows
Column A is user name,

Column B is date for example we have the month of oct and nov 2023 we always exclude saturdays

Column H is a numerical value for time spent

theres about 100k lines

since the data has 2 months oct and nov there are 8 fridays
for each user, i want the sum of column H from those fridays divided by however many fridays appear for the user

example using a filter

we have user A , we have 6 dates that are fridays , we have the sum of H from those fridays and now we divide by 6 since there are 6 fridays that appear

i need the another formula with a similar logic, instead of using friday it will be week day instead. monday thru thursday , we have 40 days total, so sum of H divided by 40

so yea 2 separate formulas where i can just manually manipulate by just replacing user and type of day, friday or weekday
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
=AVERAGE(IF(WEEKDAY(A2:A14,2)=5,B2:B14))
where 5 = friday

heres a sample, change B for H is thats the range to average
Not sure about the USER , or where that info is held

maybe
=SUMPRODUCT((WEEKDAY(A2:A14,2)=5)*(B2:B14)*(C2:C14=E2))/ SUMPRODUCT(((WEEKDAY(A2:A14,2)=5)*1)*(C2:C14=E2))

I have also showed each part of the sumproduct - so you can see the calculation

Book10
ABCDEFGHIJ
1USERsumproduct by usertotalcount
2Sun 28/01/2024111.6666667a7.5152
3Mon 29/01/20242
4Fri 02/02/20245a
5Wed 31/01/202412
6Thu 01/02/20245
7Fri 02/02/202410a
8Sat 03/02/20247
9Sun 04/02/20248
10Mon 05/02/20249
11Tue 06/02/202410
12Wed 07/02/202411
13Thu 08/02/202412
14Fri 09/02/202420
Sheet1
Cell Formulas
RangeFormula
D2D2=AVERAGE(IF(WEEKDAY(A2:A14,2)=5,B2:B14))
F2F2=SUMPRODUCT((WEEKDAY(A2:A14,2)=5)*(B2:B14)*(C2:C14=E2))/ SUMPRODUCT(((WEEKDAY(A2:A14,2)=5)*1)*(C2:C14=E2))
I2I2=SUMPRODUCT((WEEKDAY(A2:A14,2)=5)*(B2:B14)*(C2:C14=E2))
J2J2=SUMPRODUCT(((WEEKDAY(A2:A14,2)=5)*1)*(C2:C14=E2))
 
Upvote 0
I did some work if my own and decided to think out the box to get the result cause nothing seems to work and ChatGPT fails me. I need a formula to tell me for each user in column A how many of the dates are Friday. How many are Sunday and how many are weekdays. The format of the date is 10/30/23 6:00 PM. ChatGPT gave me
=COUNTIFS(A:A, "Jon", WEEKDAY(B:B, 2) = 5)
And
=COUNTIFS(A:A, "Jon", TEXT(B:B, "dddd") = "Friday")
Neither of them work….
 
Upvote 0
basically you have 2 cells. One cell has the sum of the Friday or weekday for a user, the 2nd cell will have what I need for the dates, 3rd cell will equal. I need a working formula per my previous comment since putting it all together seems near impossible
 
Upvote 0
so NOT an average but a count now

sorry confused now, not understanding what you need

=SUMPRODUCT(((WEEKDAY(B2:B14,2)=5)*1)*(A2:A14=C2))
where
5 = Friday
7 = sunday
<6 will be a weekday



Book14
ABCDEF
1UserDateUserFridaySundayWeekday
2jonSun 28/01/2024jon213
345320Mon 29/01/2024
4jonFri 02/02/2024
545322Wed 31/01/2024
645323Thu 01/02/2024
7jonFri 02/02/2024
845325Sat 03/02/2024
945326Sun 04/02/2024
10jonMon 05/02/2024
11jonTue 06/02/2024
1245329Wed 07/02/2024
13jonThu 08/02/2024
1445331Fri 09/02/2024
15
Sheet1
Cell Formulas
RangeFormula
D2D2=SUMPRODUCT(((WEEKDAY(B2:B14,2)=5)*1)*(A2:A14=C2))
E2E2=SUMPRODUCT(((WEEKDAY(B2:B14,2)=7)*1)*(A2:A14=C2))
F2F2=SUMPRODUCT(((WEEKDAY(B2:B14,2)<5)*1)*(A2:A14=C2))



A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

This will possibly enable a quicker and more accurate solution for you.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

You can also test to see if it works ok, in the "Test Here" forum.

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone
 
Upvote 0

Forum statistics

Threads
1,223,891
Messages
6,175,229
Members
452,621
Latest member
Laura_PinksBTHFT

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top