Trouble with Formula in a Table

[O'Donnell]

So I monitor my own stat's at work with a table I've created within Excel.

This provides the following for me:
row's in which I can enter a '1' or higher
Total at the end of each row (simply adds together the row)
CPH - Current Per Hour (takes the total(s) from the previous boxes for that row and any above it to work out overall average
End of Shift Total and CPH which divides my overall total by 9.5 (length of shift)

The problem I have is once I take my lunch the CPH totals on each row following are incorrect due to the Lunch row not being excluded from the formulas.

What I am trying to do is amend the formula so that if 'L' is entered into that row it is excluded from that and following rows - this should in turn mean that for the following rows the CPH will then be correct.

Is this possible?

At the moment I have:

=AVERAGE(Q1:Q2/1)

The '1' increases by 1 each row to cover the total time in the office up to 11 as I do an 11 hour shift total.

I have a programming background but my Excel knowledge is weak - I was thinking I could use an IF/OR statement e.g. =IF(Q1:Q2=L,"Lunch"(AVERAGE(Q1:Q2/1) but this doesn't work.

Attached is what I'm working with and also includes example figures + Lunch hour - As you can see the hour following my lunch the CPH row is including the Lunch hour in working out my Average - this means it is lower then the actual figure which should be '10'

Thanks

Tally Table.xlsx

[bebo021999]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[O'Donnell]

I think I've added everything you were after - could be wrong lol - let me know if more is required

[kvsrinivasamurthy]

ARRAY Formula in 2nd Row , then drag down
=SUM($Q$2:$Q2)/(ROWS($B$2:$B2)-SUM(--(ISTEXT($B$2:$B2))))