advance formula needed for avg

[Tonyk1051]

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

 

[etaf]

=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
	A	B	C	D	E	F	G	H	I	J
1					USER	sumproduct by user			total	count
2	Sun 28/01/2024	1		11.6666667	a	7.5			15	2
3	Mon 29/01/2024	2
4	Fri 02/02/2024	5	a
5	Wed 31/01/2024	12
6	Thu 01/02/2024	5
7	Fri 02/02/2024	10	a
8	Sat 03/02/2024	7
9	Sun 04/02/2024	8
10	Mon 05/02/2024	9
11	Tue 06/02/2024	10
12	Wed 07/02/2024	11
13	Thu 08/02/2024	12
14	Fri 09/02/2024	20
Sheet1

Cell Formulas
Range	Formula
D2	D2	=AVERAGE(IF(WEEKDAY(A2:A14,2)=5,B2:B14))
F2	F2	=SUMPRODUCT((WEEKDAY(A2:A14,2)=5)*(B2:B14)*(C2:C14=E2))/ SUMPRODUCT(((WEEKDAY(A2:A14,2)=5)*1)*(C2:C14=E2))
I2	I2	=SUMPRODUCT((WEEKDAY(A2:A14,2)=5)*(B2:B14)*(C2:C14=E2))
J2	J2	=SUMPRODUCT(((WEEKDAY(A2:A14,2)=5)*1)*(C2:C14=E2))

 

[Tonyk1051]

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….

 

[Tonyk1051]

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

 

[etaf]

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
	A	B	C	D	E	F
1	User	Date	User	Friday	Sunday	Weekday
2	jon	Sun 28/01/2024	jon	2	1	3
3	45320	Mon 29/01/2024
4	jon	Fri 02/02/2024
5	45322	Wed 31/01/2024
6	45323	Thu 01/02/2024
7	jon	Fri 02/02/2024
8	45325	Sat 03/02/2024
9	45326	Sun 04/02/2024
10	jon	Mon 05/02/2024
11	jon	Tue 06/02/2024
12	45329	Wed 07/02/2024
13	jon	Thu 08/02/2024
14	45331	Fri 09/02/2024
15
Sheet1

Cell Formulas
Range	Formula
D2	D2	=SUMPRODUCT(((WEEKDAY(B2:B14,2)=5)*1)*(A2:A14=C2))
E2	E2	=SUMPRODUCT(((WEEKDAY(B2:B14,2)=7)*1)*(A2:A14=C2))
F2	F2	=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.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

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

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

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