# Excel average formulae

I'm currently working on a database that keeps track of part-time employee hours and averages. I have a formula in each column to set up that shows in row 1 all hours calculated by each pay period. In row 2 I have in each column a formula calculating each weekly average for each pay period.

With hiding all formulae until data from row 1 is inputted what formula do I need, at the end of row 2, that calculates the average to that date as the data is inputted?

-

I am not sure I understand you but at least if you explain what is wrong with this answer that may help in clarifying the question. You mention “need to hide all formulas until data from row 1 is inputted” but as that is the default setting I take it you mean hide the results of formulae until ‘required’ (achieved by outputting ""). Also that row 1 already contains the required results for all hours calculated by each pay period.

The formulae in Row2 are of the kind `=IF(A1/A5=0,"",A1/A5)` to avoid displaying `0` for the average until hours have been entered for the period (whether or not zero hours).

Assuming pay periods vary in length (say either 4 or 5 weeks) and that the formula you require is for complete pay periods, I think your spreadsheet would need to include reference to how many weeks are in each pay period (Rows4:5 in the example).

`M1` contains the formula `=SUM(A1:L1)` to capture the total hours worked to the end of the last pay period (Pay Period 10 in the example). `M2` contains (I hope!) the formula you require:

``````=\$M\$1/SUM(A5:OFFSET(\$M\$1,4,-12,,MATCH(1E+100,A1:L1,1)))
``````

and displays `2`, being the sum of `A1:L1` divided by the sum of `A5:J5` (here 43).

`=OFFSET(…)` is to identify J5 and for a straightforward requirement is relatively complex, indicating that with a modified approach or layout the result may be much easier to achieve.

-