Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question
2  
What have you tried so far? –  Karthik T Jan 10 '13 at 2:18
add comment

1 Answer

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.

SU531785 example

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.