Calculating the average – several variables to consider

[jordanm14]

Hi there,

I’m trying to figure out how to properly calculate the average total hours worked in this table.

A few things to consider…

1.  Some individuals will only have hours entered for week 1. Therefore, if the individual only has hours entered in week 1, the cell beside “average of total hours worked” should only pull the average of week 1.

However, some individuals will have hours entered in both week 1 and week 2. Therefore, the cell beside “average of total hours worked” should pull the average of weeks 1 and 2.

2. As displayed in the screenshots above, not all cells will be filled in. For example, depending on the individual, they may work Sunday, Monday, Tuesday, Wednesday during week 1; whereas some individuals may work Monday, Tuesday, Wednesday, Thursday during week 1 and then work Monday, Tuesday, Wednesday during week 2.

With these conditions in mind, and factoring in the variables, is it possible to create a script that will accurately calculate the average total hours worked?

Thanks!

[Nesa Nurani]

Yes, that is possible using JavaScript.

If you need help with the script, tell us field names or share your file.

[jordanm14]

Hi @Nesa Nurani ,

Please see below screenshot for the field names:

Let me know if you need anything else. Appreciate your help!

[Nesa Nurani]

Use this as 'Custom calculation script' of "Avg Total Hours" field:

var fields1 = ["Sun1", "Mon1", "Tue1", "Wed1", "Thur1", "Fri1", "Sat1"];
var fields2 = ["Sun2", "Mon2", "Tue2", "Wed2", "Thur2", "Fri2", "Sat2"];
var t1 = 0;
var t2 = 0;
var avg1 = 0;
var avg2 = 0;
var total1 = this.getField("Total1");
var total2 = this.getField("Total2");

for(var i=0; i<fields1.length; i++){
 var f1 = Number(this.getField(fields1[i]).valueAsString);
 var f2 = Number(this.getField(fields2[i]).valueAsString);
 if(!isNaN(f1) && f1 !== 0){
  avg1++;
  t1 += f1;}
 if(!isNaN(f2) && f2 !== 0){
  avg2++;
  t2 += f2;}}

total1.value = (avg1 !== 0) ? t1/avg1 : 0;
total2.value = (avg2 !== 0) ? t2/avg2 : 0;

event.value = Number(total1.valueAsString)+Number(total2.valueAsString);

[jordanm14]

Hi @Nesa Nurani ,

I used your script but it the calculations were incorrect. My apologies, I didn't do a great job of explaining what I'm looking for. I'm trying to calculate the average number of hours worked per week, not per day.

Essentially, I'm using this table to calculate if employees are entitled to overtime. Under an averaging arrangement, an employee is entitled to overtime if their hours of work exceed 44 hours in a 1 week period, or exceed an average of 44 hours in a multi-week period (i.e., 2 weeks)

The "Total1" cell should calculate the sum of all numbers entered in the 'Week 1 Hours' row.
The "Total2" cell should calculate the sum of all numbers entered in 'Week 2 Hours' row (if applicable).
If hours are only entered in Week 1, the "Avg Total Hours" cell should pull the value of 'Total1' (see screenshot for example):

However, if hours are entered in both Week 1 and Week 2, the "Avg Total Hours" cell should calculate the average of 'Total1' and 'Total2' (see screenshot for example):

Thanks!

[try67]

In both cases you need a script that ignores empty values, instead of treating them like zero.

Search the forum for "calcAverage". It's a generic function I wrote that allows you to do just that.

You only need to supply it with a list of field names and some basic parameters, and you could re-use the same code for all three calculations.

[jordanm14]

Hi @try67 ,

I tried using your "calcAverage" script, however the average isn't appearing in the designated field - it's blank. I've included my custom calculation script below. Any idea why this is happening? I'm very new to this, so it's difficult for me to determine what's causing the error.

[try67]

The last line should be:

event.value = calcAverage(...);

[jordanm14]

@try67 Like this? If so, it's still not showing up.

[try67]

Did you place the code as that field's Custom Calculation script?

[jordanm14]

@try67 Yes I did:

[try67]

Check the JS Console for error messages. If you still have issues, share the file with us.

[jordanm14]

@try67 Please see attached file.

[try67]

There were some errors in my code. I fixed them in the attached file. If you let me know where you copied it from I'll fix it there as well...

[jordanm14]

Thanks @try67 !

I copied it from this article: https://community.adobe.com/t5/acrobat-discussions/formula-to-calculate-the-average-of-a-number-of-f...

[try67]

Thanks, I fixed it there, too.

[Nesa Nurani]

Script already calculates Total1 and Total2 average, it doesn't include empty fields or fields with 0.

Here is an updated script to calculate total average too:

var fields1 = ["Sun1", "Mon1", "Tue1", "Wed1", "Thur1", "Fri1", "Sat1"];
var fields2 = ["Sun2", "Mon2", "Tue2", "Wed2", "Thur2", "Fri2", "Sat2"];
var t1 = 0;
var t2 = 0;
var avg1 = 0;
var avg2 = 0;
var total1 = this.getField("Total1");
var total2 = this.getField("Total2");

for(var i=0; i<fields1.length; i++){
 var f1 = Number(this.getField(fields1[i]).valueAsString);
 var f2 = Number(this.getField(fields2[i]).valueAsString);
 if(!isNaN(f1) && f1 !== 0){
  avg1++;
  t1 += f1;}
 if(!isNaN(f2) && f2 !== 0){
  avg2++;
  t2 += f2;}}

total1.value = (avg1 !== 0) ? t1/avg1 : "";
total2.value = (avg2 !== 0) ? t2/avg2 : "";

var totalAvg = 0;
var tAvg = 0;
if(!isNaN(total1.value)&& Number(total1.valueAsString) !== 0){
totalAvg += Number(total1.valueAsString);
tAvg++;}
if(!isNaN(total2.value)&& Number(total2.valueAsString) !== 0){
totalAvg += Number(total2.valueAsString);
tAvg++;}
if(tAvg !== 0)
event.value = totalAvg/tAvg;
else
event.value = "";

[jordanm14]

Hi @Nesa Nurani,

I think I'm making this more complicated than it needs to be. I currently have two calculation in place. The first calculates the sum of 'Week 1 Hours'; the second calculates the sum of 'Week 2 Hours'. I then added a third that calculates the average of 'Total Hours Worked'.

Is it possible to show the value of the 'Avg Total Hours' field only if hours are entered in both Week 1 and Week 2? In other words, if no hours are entered in the 'Week2' row, can the value of the 'Avg Total Hours' field be hidden? 

[Nesa Nurani · Accepted solution]

In that case for "Total1" and "Total2" fields you can use built in calculation, go to those fields properties and under 'Calculate' tab select 'Value is the' and pick fields you want to sum. Then as custom calculation script of total average field use this:

var total = 0;
var avg = 0;

for(var i=1; i<=2; i++){
 var f = Number(this.getField("Total"+i).valueAsString);
 if(!isNaN(f) && f !== 0){
  avg++;
  total += Number(f);}}

if(avg !== 0)
 event.value = total/avg;
else
 event.value = 0;

[jordanm14]

Thanks @Nesa Nurani ! Is it possible to hide the value of the 'Avg Total Hours' field if the 'Total2' field is 0?

Essentially, I only want the 'Avg Total Hours' value to be visible if there are values in both the 'Total1' and 'Total2' fields.

[Nesa Nurani]

Try this:

var total = 0;
var avg = 0;

for(var i=1; i<=2; i++){
 var f = Number(this.getField("Total"+i).valueAsString);
 if(!isNaN(f) && f !== 0){
  avg++;
  total += Number(f);}}

if(avg === 2)
 event.value = total/avg;
else
  event.value = "";

[jordanm14]

That worked, thank you so much!