Criteria-based Calculation of the Average Amount of Working Hours

[MalumMorale]

Hey everyone,

I'm struggling with trying to correct a formula which is currently delivering a wrong result or at least not the result I am supposed to achieve.
The problem is that the current formula divides by the number of all rows / bookings with the same "contract number" on sheet1.
Thereby the output is wrong for projects with more than one booking since it doesn't deliver the avg amount of working hours / month but the avg per booking.




I've already come up with several different new formulas but neither of them was working the way they were intended to. I'm not sure if what I'm trying to achieve is possible with the current individual excel formulas I'm using or tried using and if so in which order I need to apply them. I'd think calculating the average should not be so difficult, just setting the right data criteria while doing so.

Current/wrong formula for column avg working hours / month:

• =IFERROR(AVERAGEIFS(sheet1[working hours];sheet1[contract number];[@[contract number]]);

To better understand what the data looks like I'll give an example with dummy datasets and only add data for one dummy project since it's most likely the best way to visualize the data for clarification.

sheet1/total accounted working hours:


[TABLE="width: 500"]
<tbody>[TR]
[TD]bill number[/TD]
[TD]bill date[/TD]
[TD]contract number[/TD]
[TD]project name[/TD]
[TD]accounting period[/TD]
[TD]person[/TD]
[TD]working hours[/TD]
[/TR]
[TR]
[TD]2019-005
[/TD]
[TD]01.02.2019[/TD]
[TD]1000-1 [/TD]
[TD]mr. excel[/TD]
[TD]01.01.2019[/TD]
[TD]A. B.[/TD]
[TD]114,0[/TD]
[/TR]
[TR]
[TD]2019-005[/TD]
[TD]01.02.2019[/TD]
[TD]1000-1 [/TD]
[TD]mr. excel[/TD]
[TD]01.01.2019[/TD]
[TD]C. D.[/TD]
[TD]80,4[/TD]
[/TR]
[TR]
[TD]2019-005[/TD]
[TD]01.02.2019[/TD]
[TD]1000-1 [/TD]
[TD]mr. excel[/TD]
[TD]01.01.2019[/TD]
[TD]E. F.[/TD]
[TD]70,4[/TD]
[/TR]
[TR]
[TD]2019-005[/TD]
[TD]01.02.2019[/TD]
[TD]1000-1 [/TD]
[TD]mr. excel[/TD]
[TD]01.01.2019[/TD]
[TD]G. H.[/TD]
[TD]90,6[/TD]
[/TR]
[TR]
[TD]2019-070[/TD]
[TD]01.03.2019[/TD]
[TD]1000-1 [/TD]
[TD]mr. excel[/TD]
[TD]01.02.2019[/TD]
[TD]A. B.[/TD]
[TD]120,3[/TD]
[/TR]
[TR]
[TD]2019-070[/TD]
[TD]01.03.2019[/TD]
[TD]1000-1 [/TD]
[TD]mr. excel[/TD]
[TD]01.02.2019[/TD]
[TD]C. D.[/TD]
[TD]179,3[/TD]
[/TR]
[TR]
[TD]2019-070[/TD]
[TD]01.03.2019[/TD]
[TD]1000-1 [/TD]
[TD]mr. excel[/TD]
[TD]01.02.2019[/TD]
[TD]E. F.[/TD]
[TD]150,2[/TD]
[/TR]
[TR]
[TD]2019-070[/TD]
[TD]01.03.2019[/TD]
[TD]1000-1 [/TD]
[TD]mr. excel[/TD]
[TD]01.02.2019[/TD]
[TD]G. H.[/TD]
[TD]110,5[/TD]
[/TR]
[TR]
[TD]2019-089[/TD]
[TD]01.04.2019[/TD]
[TD]1000-1 [/TD]
[TD]mr. excel[/TD]
[TD]01.03.2019[/TD]
[TD]A. B.[/TD]
[TD]80,3[/TD]
[/TR]
[TR]
[TD]2019-089[/TD]
[TD]01.04.2019[/TD]
[TD]1000-1 [/TD]
[TD]mr. excel[/TD]
[TD]01.03.2019[/TD]
[TD]C. D.[/TD]
[TD]60,3[/TD]
[/TR]
[TR]
[TD]2019-091[/TD]
[TD]01.05.2019[/TD]
[TD]1000-1 [/TD]
[TD]mr. excel[/TD]
[TD]01.04.2019[/TD]
[TD]A. B.[/TD]
[TD]140,9[/TD]
[/TR]
[TR]
[TD]2019-091[/TD]
[TD]01.05.2019[/TD]
[TD]1000-1 [/TD]
[TD]mr. excel[/TD]
[TD]01.04.2019[/TD]
[TD]C. D.[/TD]
[TD]100,3[/TD]
[/TR]
[TR]
[TD]2019-091[/TD]
[TD]01.05.2019[/TD]
[TD]1000-1 [/TD]
[TD]mr. excel[/TD]
[TD]01.04.2019[/TD]
[TD]E. F.[/TD]
[TD]99,9[/TD]
[/TR]
[TR]
[TD]2019-091[/TD]
[TD]01.05.2019[/TD]
[TD]1000-1 [/TD]
[TD]mr. excel[/TD]
[TD]01.04.2019[/TD]
[TD]G. H.[/TD]
[TD]97,5[/TD]
[/TR]
[TR]
[TD]2019-091[/TD]
[TD]01.05.2019[/TD]
[TD]1000-1 [/TD]
[TD]mr. excel[/TD]
[TD]01.04.2019[/TD]
[TD]I. J.[/TD]
[TD]40,3[/TD]
[/TR]
[TR]
[TD]2019-091[/TD]
[TD]01.05.2019[/TD]
[TD]1000-1 [/TD]
[TD]mr. excel[/TD]
[TD]01.04.2019[/TD]
[TD]K. L.[/TD]
[TD]60,6[/TD]
[/TR]
</tbody>[/TABLE]



sheet2/controlling:


[TABLE="width: 500"]
<tbody>[TR]
[TD]contract number[/TD]
[TD]project name[/TD]
[TD]avg working hours / month[/TD]
[TD]state[/TD]
[/TR]
[TR]
[TD]1000-1[/TD]
[TD]mr. excel[/TD]
[TD]0,00 ???[/TD]
[TD]ok[/TD]
[/TR]
</tbody>[/TABLE]

Please let me know if you need more information or have a fix for my problem!


I'll be looking forward for your responses! Thanks for taking your time to help me out!


- Stephan

 

[DanteAmor]

Try this

<b>Sheet2</b><br /><br /><table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:117.86px;" /><col style="width:98.85px;" /><col style="width:177.74px;" /><col style="width:50.38px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >contract number</td><td >project name</td><td >avg working hours / month</td><td >state</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">1000-1</td><td >mr. excel</td><td style="text-align:right; ">99.7375</td><td >ok</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >C2</td><td >=AVERAGEIF(Tabla1[contract number],[contract number],Tabla1[working hours])</td></tr></table></td></tr></table>

 

[MalumMorale]

Dear DanteAmor,

thanks for taking your time to reply!

I think I need to rewrite my question since it probably was not quite clear what I'm trying to do, please let me try to put it into different words.

I'm not sure if the function AVERAGEIF covers all the criterias I need to check so I'm trying to to break the problem down into all the single calculation steps and combine them.

I need to sum up all the working hours on sheet1 for a specific contract number (there are different contract numbers for ever single project since every year each project gets a new consecutive contract number assigned, since it's a new commission).
Then I want to divide the sum of working hours by the count of distinct bill numbers which are linked to the same contract number.

I hope this clarifies my problem.

Best regards,
Stephan

 

[DanteAmor]

Try with this data

Sheet1

Excel Workbook
	A	B	C	D	E	F	G
1	bill number	bill date	contract number	project name	accounting period	person	working hours
2	2019-005	01.02.2019	1000-1	mr. excel	01.01.2019	A. B.	114
3	2019-005	01.02.2019	1000-1	mr. excel	01.01.2019	C. D.	80.4
4	2019-005	01.02.2019	1000-1	mr. excel	01.01.2019	E. F.	70.4
5	2019-005	01.02.2019	1000-1	mr. excel	01.01.2019	G. H.	90.6
6	2019-070	01.03.2019	1000-1	mr. excel	01.02.2019	A. B.	120.3
7	2019-070	01.03.2019	1000-1	mr. excel	01.02.2019	C. D.	179.3
8	2019-070	01.03.2019	1000-1	mr. excel	01.02.2019	E. F.	150.2
9	2019-070	01.03.2019	1000-1	mr. excel	01.02.2019	G. H.	110.5
10	2019-089	01.04.2019	1000-1	mr. excel	01.03.2019	A. B.	80.3
11	2019-089	01.04.2019	1000-1	mr. excel	01.03.2019	C. D.	60.3
12	2019-091	01.05.2019	1000-1	mr. excel	01.04.2019	A. B.	140.9
13	2019-091	01.05.2019	1000-1	mr. excel	01.04.2019	C. D.	100.3
14	2019-091	01.05.2019	1000-1	mr. excel	01.04.2019	E. F.	99.9
15	2019-091	01.05.2019	1000-1	mr. excel	01.04.2019	G. H.	97.5
16	2019-091	01.05.2019	1000-2	mr. excel	01.04.2019	I. J.	40.3
17	2019-091	01.05.2019	1000-2	mr. excel	01.04.2019	K. L.	60.6
Sheet

----------------------------------------------------------------------------

Excel Workbook
	A	B	C	D
1	contract number	project name	avg working hours / month	state
2	1000-1	mr. excel	,Tabla1) / COUNT(IF(FREQUENCY(IF(Tabla1=Tabla2,],MATCH(Tabla1,Tabla1,0)),ROW(Tabla1)-ROW($B2)+1),1))}]373.725	ok
3	1000-2	mr. excel	50.45
Sheet2

Array formulas
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

 

[MalumMorale]

Dear DanteAmor,

first of all, thank you for replying and helping me out! Your formula works as anticipated. Nevertheless I've only just realized I misunderstood the problem myself. I hope you can help me out anyway.

Problem / Current result:

As I said the problem is that my current formula result delivers the average amount of working hours per booking (= average of all bookings with the same contract number). But it's actually supposed to output the average working hours per month.

What I tried first:

I've tried using a bunch of different formulas and ways for calculating the average and check if the contract numbers on both sheets match. But I was stuck since I misunderstood the task and had problems checking the criterias and concatenating the formulas.

Explanation:

The calculation consists of finding the average working hours per bill numbers with the same contract number (This contract number has to match the one of the current row in Sheet2). Then again the average of this set of average numbers has to be calculated. That would be the expected result - the average per month.
(To be honest, this calculation seemed questionable to me because I didn’t understand the meaning of building an average using a set of numbers which are already average numbers.)

What I tried then:

Since I had problems building the formula I tried getting to the result in a different way. I created a pivot table from the dataset in Sheet1 and grouped/ordered it in a way that only party solves my problem.
Now the grouping looks like this: project -> contract number -> bill numbers (for this contract number) paired with each bill numbers average working hours.

Expected result:

By manually selecting each bill numbers average working hours for one specific contract number it shows me average of those in the bottom right corner of the Excel window. This is my expected result. So what I want is to automatize this. Therefore I need a formula to access the fields in the pivot table. I would gladly accept any other recommendations!
(On top of it, the pivot table also calculates the average amount of working hours per booking which was the result in my current formula but not the one I'm searching for.)
Is there any way to even access those bill number averages which are grouped by the contract number? Because unlike my other tables, there’s no suggestion coming up for referencing to the pivot table when I’m using the formula builder. I assume I need to use the GETPIVOTDATA function but I don't know how to apply it in the correct way.

For visualization purposes:

Pivot Table in Sheet3:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Average working hours[/TD]
[TD]Column labeling[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row Labeling[/TD]
[TD]<01.02.2017[/TD]
[TD]2017[/TD]
[TD]2018[/TD]
[TD]2019[/TD]
[TD]Total result[/TD]
[/TR]
[TR]
[TD]project mr. excel[/TD]
[TD]157,5[/TD]
[TD]111,53[/TD]
[TD]93,29[/TD]
[TD]86,94[/TD]
[TD]98,80[/TD]
[/TR]
[TR]
[TD]+ 1000-1[/TD]
[TD]157,5[/TD]
[TD]111,53[/TD]
[TD]93,41[/TD]
[TD][/TD]
[TD]101,95[/TD]
[/TR]
[TR]
[TD]- 1000-2[/TD]
[TD][/TD]
[TD][/TD]
[TD]91[/TD]
[TD]86,94[/TD]
[TD]87,36[/TD]
[/TR]
[TR]
[TD]2018-001[/TD]
[TD][/TD]
[TD][/TD]
[TD]91[/TD]
[TD][/TD]
[TD]91[/TD]
[/TR]
[TR]
[TD]2019-008[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]107,25[/TD]
[TD]107,25[/TD]
[/TR]
[TR]
[TD]2019-054[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]83,33[/TD]
[TD]83,33[/TD]
[/TR]
[TR]
[TD]2019-083[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]111,5[/TD]
[TD]111,5[/TD]
[/TR]
[TR]
[TD]2019-104[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]54,83[/TD]
[TD]54,83[/TD]
[/TR]
[TR]
[TD]2019-163[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]38,5[/TD]
[TD]38,5[/TD]
[/TR]
</tbody>[/TABLE]

(The + just means the group is collapsed and can be expended, whereas the - one already is.)

By selecting and building the average of the averages (91; 107,25; 83,33; 111,5; 83,33; 111,5; 54,8 and 38,5) --> average working hours / month = 81,069....
This is the number I need in Sheet2 for the project.

(Sheet1 and Sheet2 still have the same structure as initially given)

I just modified the dummy data, I hope this leads to no confusion.

Best regards,
Stephan

 

[MalumMorale]

I solved the problem by myself using PowerQuery and a workaround formula.

Best regards,
Stephan

 

[DanteAmor]

Congratulations, thanks for the feedback!