Glitches in Excel formulas

Question

02.07.24 Fd Wge Cost Bgt 24-25 - 10 (version 8 13.05.24.xml

Hi,

We have a problem with the attached spreadsheet.

The details are as follows:

Daily Spreadsheet : "Monday" Wages

Q2985 "Minimum" formula for Mondays only works with Q2821

Q2986 "Average" formula for Mondays only works with Q1013 (Actual data entered to 30th June 2024)

Q2987 "Max" formula for Mondays only works with Q2821

What is special about row 2821?

1013 is the last row for which actual data was entered for 30th June 2024

Every other day of the week

Tuesday-Sunday

Minimum works with 2981 last line in spreadsheet

Average works with 2981 last line in spreadsheet

Max works with 2981 last line in spreadsheet

Calculation of Average wage cost per cover Column AA

AA2985 only works with AA1013

AA2986 only works with AA1013

AA2987 only works with AA1013

All 3 formulas should work with AA2981

Perhaps if the formulas are tweaked it would not be necessary to put in row 1013 manually every time and 2981 would work in all cases.

We cannot see any logic in having to use row 2821 in some formulas.

Regards,

Laurence

Answer 1

Your spreadsheet has circular references. By itself, that is not necessarily a problem. In this case, your spreadsheet has iterative calculations disabled. That makes it a problem.

To fix it, open the spread sheet, click on File, click on Options at the bottom of the left column, click on Formulas in the pop-up window, and click to put a check mark in Enable iterative calculation at the top right. I have mine set to 100 iterations and .001 change.

With these options, changing Q2985 to =MIN(FILTER(Q13:Q2981,Q13:Q2981>0)) works as expected.

Answer 2

You might want to double-check the data range in your Monday wage formulas—it's possible the references are not dynamically updating past Q2821 or Q1013. For a more seamless verification of pay calculations, try using the ADP Wage Calculator for Weekly Wages to compare values and ensure your manual spreadsheet aligns with estimated outputs. It helps validate averages, mins, and maxes effectively.