How to write shorter formula for the following average calculation?

D

Dezom

New Member
Joined
Jul 21, 2020
Messages
21
Office Version
  1. 2016
Platform
  1. Windows
  2. Mobile
Hello Everyone,
In the following table, What I want is to get the values of "Average of the Duration for Second work and New Work concepts in each week for John". For that I have used two times SUMIFS because of the two different concepts of work and I have used one time SUMPRODUCT formula. How would we write that with shorter? Thank you



Countifs question.xlsx
ABCDEFGHIJKLM
1WorkerWeekConceptDurationAverage Duration
2John1.weekCorrection20Average of the Duration for Second work and New Work concepts in each week for John
3John1.weekSecond work20
4John1.weekNew Work201.week17,333333
5Karhan2.weekMK202.week#SAYI/0!
6John1.weekNew Work123.week#SAYI/0!
7Kylee2.weekSK124.week#SAYI/0!
8Tomas6.weekSK205.week#SAYI/0!
9John2.weekSK126.week15
10Mattheus3.weekSK127.week#SAYI/0!
11Dennis3.weekSK12
12Markus3.weekSK20
13Tomas4.weekNew Work14
14Karhan4.weekNew Work2
15Mattheus4.weekNew Work4
16Karhan4.weekNew Work2
17Karhan5.weekCorrection14
18John5.weekCorrection5
19Kylee5.weekCorrection3
20Kylee5.weekCorrection6
21Kylee5.weekNew Work2
22Jonnathan5.weekNew Work0
23Daniel5.weekSecond work22
24Solomon6.weekProduction Sequence3
25John6.weekSecond work15
26Mattheus6.weekNew Work18
27Kylee7.weekNew Work10
28Dennis7.weekSecond work14
29Solomon7.weekNew Work14
30Jonnathan7.weekNew Work14
Data
Cell Formulas
RangeFormula
H4:H10H4=(SUMIFS($D$2:$D$30,$C$2:$C$30,"Second work",$B$2:$B$30,G4,$A$2:$A$30,"John")+ SUMIFS($D$2:$D$30,$C$2:$C$30,"New Work",$B$2:$B$30,G4,$A$2:$A$30,"John")) /SUMPRODUCT(($B$2:$B$30=G4)*($C$2:$C$30={"Second work","New Work"})*($A$2:$A$30="John"))
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Sort by date Sort by votes
Book1
ABCDEFGHIJK
1WorkerWeekConceptDurationAverage Duration
2John1.weekCorrection20Average of the Duration for Second work and New Work concepts in each week for John
3John1.weekSecond work20
4John1.weekNew Work201.week17.33333317.33333317.33333317.333333
5Karhan2.weekMK202.week#DIV/0!#DIV/0!#DIV/0!#DIV/0!
6John1.weekNew Work123.week#DIV/0!#DIV/0!#DIV/0!#DIV/0!
7Kylee2.weekSK124.week#DIV/0!#DIV/0!#DIV/0!#DIV/0!
8Tomas6.weekSK205.week#DIV/0!#DIV/0!#DIV/0!#DIV/0!
9John2.weekSK126.week15151515
10Mattheus3.weekSK127.week#DIV/0!#DIV/0!#DIV/0!#DIV/0!
11Dennis3.weekSK12
12Markus3.weekSK20
13Tomas4.weekNew Work14
14Karhan4.weekNew Work2
15Mattheus4.weekNew Work4
16Karhan4.weekNew Work2
17Karhan5.weekCorrection14
18John5.weekCorrection5
19Kylee5.weekCorrection3
20Kylee5.weekCorrection6
21Kylee5.weekNew Work2
22Jonnathan5.weekNew Work0
23Daniel5.weekSecond work22
24Solomon6.weekProduction Sequence3
25John6.weekSecond work15
26Mattheus6.weekNew Work18
27Kylee7.weekNew Work10
28Dennis7.weekSecond work14
29Solomon7.weekNew Work14
30Jonnathan7.weekNew Work14
Sheet3
Cell Formulas
RangeFormula
H4:H10H4=(SUMIFS($D$2:$D$30,$C$2:$C$30,"Second work",$B$2:$B$30,G4,$A$2:$A$30,"John")+ SUMIFS($D$2:$D$30,$C$2:$C$30,"New Work",$B$2:$B$30,G4,$A$2:$A$30,"John")) /SUMPRODUCT(($B$2:$B$30=G4)*($C$2:$C$30={"Second work","New Work"})*($A$2:$A$30="John"))
I4:I10I4=AVERAGEIFS($D$2:$D$30,$C$2:$C$30,"*work",$B$2:$B$30,G4,$A$2:$A$30,"John")
J4:J10J4=AVERAGE(IF($B$2:$B$30=G4,IF($A$2:$A$30="John",IF(ISNUMBER(MATCH($C$2:$C$30,{"Second work","New Work"},)),$D$2:$D$30))))
K4:K10K4=SUMPRODUCT(SUMIFS($D$2:$D$30,$C$2:$C$30,{"Second work","New Work"},$B$2:$B$30,G4,$A$2:$A$30,"John"))/SUMPRODUCT(COUNTIFS($C$2:$C$30,{"Second work","New Work"},$B$2:$B$30,G4,$A$2:$A$30,"John"))
 
Upvote 0
How about
Excel Formula: 
=SUM(SUMIFS($D$2:$D$30,$C$2:$C$30,{"Second work","New Work"},$B$2:$B$30,G4,$A$2:$A$30,"John"))/SUM(COUNTIFS($C$2:$C$30,{"Second work","New Work"},$B$2:$B$30,G4,$A$2:$A$30,"John"))
 
Upvote 0
Hello Bo_Ry Thanks a lor.

The last one is not working in my excel. And I do not understand why my excel works with ";" but yours "," I am using pro 2016.
=SUMPRODUCT(SUMIFS($D$2:$D$30,$C$2:$C$30,{"Second work","New Work"},$B$2:$B$30,G4,$A$2:$A$30,"John"))/SUMPRODUCT(COUNTIFS($C$2:$C$30,{"Second work","New Work"},$B$2:$B$30,G4,$A$2:$A$30,"John"))
 
Upvote 0
Fluff said:
How about
Excel Formula: 
=SUM(SUMIFS($D$2:$D$30,$C$2:$C$30,{"Second work","New Work"},$B$2:$B$30,G4,$A$2:$A$30,"John"))/SUM(COUNTIFS($C$2:$C$30,{"Second work","New Work"},$B$2:$B$30,G4,$A$2:$A$30,"John"))
Click to expand...

Hello Fluff thats not working. thats giving me error with that formula. It is totally same as what you have written
 
Upvote 0
That gives me the same answers as your formula
+Fluff New.xlsm
ABCDEFGHI
1WorkerWeekConceptDurationAverage Duration
2John1.weekCorrection20Average of the Duration for Second work and New Work concepts in each week for John
3John1.weekSecond work20
4John1.weekNew Work201.week17.333333317.3333333
5Karhan2.weekMK202.week#DIV/0!#DIV/0!
6John1.weekNew Work123.week#DIV/0!#DIV/0!
7Kylee2.weekSK124.week#DIV/0!#DIV/0!
8Tomas6.weekSK205.week#DIV/0!#DIV/0!
9John2.weekSK126.week1515
10Mattheus3.weekSK127.week#DIV/0!#DIV/0!
11Dennis3.weekSK12
12Markus3.weekSK20
13Tomas4.weekNew Work14
14Karhan4.weekNew Work2
15Mattheus4.weekNew Work4
16Karhan4.weekNew Work2
17Karhan5.weekCorrection14
18John5.weekCorrection5
19Kylee5.weekCorrection3
20Kylee5.weekCorrection6
21Kylee5.weekNew Work2
22Jonnathan5.weekNew Work0
23Daniel5.weekSecond work22
24Solomon6.weekProduction Sequence3
25John6.weekSecond work15
26Mattheus6.weekNew Work18
27Kylee7.weekNew Work10
28Dennis7.weekSecond work14
29Solomon7.weekNew Work14
30Jonnathan7.weekNew Work14
Data
Cell Formulas
RangeFormula
H4:H10H4=(SUMIFS($D$2:$D$30,$C$2:$C$30,"Second work",$B$2:$B$30,G4,$A$2:$A$30,"John")+ SUMIFS($D$2:$D$30,$C$2:$C$30,"New Work",$B$2:$B$30,G4,$A$2:$A$30,"John")) /SUMPRODUCT(($B$2:$B$30=G4)*($C$2:$C$30={"Second work","New Work"})*($A$2:$A$30="John"))
I4:I10I4=SUM(SUMIFS($D$2:$D$30,$C$2:$C$30,{"Second work","New Work"},$B$2:$B$30,G4,$A$2:$A$30,"John"))/SUM(COUNTIFS($C$2:$C$30,{"Second work","New Work"},$B$2:$B$30,G4,$A$2:$A$30,"John"))
 
Upvote 0
Dezom said:
I do not understand why my excel works with ";" but yours ","
Click to expand...


Different regions use different array separators.
For UK, US
Columns ,
Rows ;

For EU
Columns /
Rows ;

Change to / or ; should work.
{"Second work"/"New Work"}
 
Upvote 0
with Power Query
WorkerWeekConceptDurationWorkerWeekAvg
John1.weekCorrection20John1.week17.33333333
John1.weekSecond work20John6.week15
John1.weekNew Work20
Karhan2.weekMK20
John1.weekNew Work12
Kylee2.weekSK12
Tomas6.weekSK20
John2.weekSK12
Mattheus3.weekSK12
Dennis3.weekSK12
Markus3.weekSK20
Tomas4.weekNew Work14
Karhan4.weekNew Work2
Mattheus4.weekNew Work4
Karhan4.weekNew Work2
Karhan5.weekCorrection14
John5.weekCorrection5
Kylee5.weekCorrection3
Kylee5.weekCorrection6
Kylee5.weekNew Work2
Jonnathan5.weekNew Work0
Daniel5.weekSecond work22
Solomon6.weekProduction Sequence3
John6.weekSecond work15
Mattheus6.weekNew Work18
Kylee7.weekNew Work10
Dennis7.weekSecond work14
Solomon7.weekNew Work14
Jonnathan7.weekNew Work14

Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Filter = Table.SelectRows(Source, each ([Worker] = "John") and ([Concept] = "New Work" or [Concept] = "Second work")),
    Group = Table.Group(Filter, {"Worker", "Week"}, {{"Avg", each List.Average([Duration]), type number}})
in
    Group
 
Upvote 0
what about
1599417762757.png
 
Upvote 0
also you can use "short" :) Pivot Table
Concept(Multiple Items)
Average of DurationWeek
Worker1.week6.week
John17.3333333315

ptf.png
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question
Golf Two Man Low Net Score Game-Calculate Payout for top 3 teams including team ties
Replies
5
Views
215
Golfhacker
G
I
  • Question
Tracking vacation requests
Replies
9
Views
496
KRice
K
zookeepertx
  • Question
Having trouble "deciphering" some code so I can see how it works
Replies
7
Views
99
zookeepertx
zookeepertx
O
  • Question
Adding new items to dataset that a UNIQUE function pulls from breaks adjacent columns
Replies
1
Views
156
SunnyAlv
SunnyAlv
B
  • Solved
Array Returning "0" value
Replies
5
Views
213
Fluff
Fluff

Forum statistics

Threads
1,215,507
Messages
6,125,201
Members
449,214
Latest member
mr_ordinaryboy

Share this page

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