How to sum a fixed # of cells

[fcf]

Hi

I have a spreadsheet that contains hours worded by day for a calendar year

I need to be able to calculate the total hours worked over a period of 10 days.

How can I do so when the data is layed out as follows

[pre]

month jan 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23 and so on
hours-----5,8,3,6,7, ,6,7,8,06,07,06,08,04,06,05,  ,02,08,00,08,06,09,

[/pre]
the formula needs to count the hours worked backwards from Jan23 until it reaches a

total count of 10 days worked. Where the cell is null or empty it has to move to the next

cell location until it counts 10 cells with values.

once we have the 10 cells with values it sums the total hours workd and divides it by

10 for the average hours worked.

i hope i have explained this clearly.

Regards

fcf

 

[Luke M]

to clarify, should day 20 be counted since it has 0 hours? or no?

 

[jason]

what if you had a 'running calculation' under the hrs worked?

Assuming you had Jan1 in A1 and 5hrs worked in A2:

You could start the formula in J6 (the 10th day of the month).

=sum(J5:A5)/10


then you could drag that formula across for the remaining days...? this would show you from any particular day, what the average hrs worked for the past 10 days was.

this would, of course, count empty cells as 0.... in regards to Luke's question. also, dont know if this will be help....mainly depend on how you want the data.


month jan 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23 and so on

hours-----5,8,3,6,7, ,6,7,8,06,07,06,08,04,06,05, ,02,08,00,08,06,09,


also, if you're looking at the past 20 days, why do you want it to 'search' for the last 10 days and not just 20?

what would you do at the end of the month; like with the Jan31... it will just beyond the second 20day period?? will the following month start in the next column?

 

[fcf]

Hi Luke

if the cell has a null value it is not counted, with that in mind i can only

add 10 days values. it may happen that someone is off for two days in a 10 day period so the formula must look to the next two dates for a value greater the 0 and add them to the total

 

[fcf]

the spreadsheet is used to calculate stat holiday pay. i must look at the last 10 days worked prior to the stat day to determine the average hours worked to calculate the amount of stat holiday pay. This causes a problem if someone one is off within the last 10 days, so i have to then add the next day worked hours.

oh, and sorry for the confusion, it was suppose to be 10 days not 20

 

[Faseeh]

Use this array formula, ur data is in A1:B23......


SUM(LOOKUP(LARGE(IF(ISBLANK(B1:B23)=FALSE,ROW(1:23)),ROW(1:10)),ROW(1:23),IF(ISBLANK(B1:B23)=FALSE,B1:B23)))/10

 

[Luke M]

Modifying Faseeh's formula for horizontal arrangement in row 2:

=SUM(LOOKUP(LARGE(IF(ISNUMBER(2:2),COLUMN(2:2)),ROW(1:10)),COLUMN(2:2),IF(ISNUMBER(2:2),2:2)))/10

 

[fcf]

Hi Faseeh

thanks for the help. but i get an answer of 1 when i used the formula you sent

 

[Faseeh]

Hi fcf,


I mentioned in my post that it is "Array" formula so you should execute it pressing "CTRL+SHIFT+ENTER". Both mine & Luke's formula are giving correct results. Without this it will give you 0.1.


Regards,