Need help with Excel forumlas

[Viper87227]

I am working on a spreadsheet for my work and am having problems with one of the formulas I am trying to implement. The purpose of the spreadsheet is an employee audit tracker... its fairly simple. I audit their work and give a score 1-5. I also keep a running average of their scores (and some other information that's not pertinent to what im trying to accomplish here.) I'm trying to automate as much of the mathmatical side as possible and the average is giving me a headache.

Assume the audit score is row 5 on my sheet and the average is row six. The scores start in column B and move out from there. Assume I have three scores entered, so B5-E5 are populated. I've got a forumlar in place in cell E6 that says if E5 has a value, calculate theaverage from B5-E5. My formula looks like "=IF(E5>0, AVERAGE(B5:E5)," "). This formula works fine when I enter a number into E5. Where I am having an issue is copying the formula. Say I take this formua in cell E6 and copy it to F6, my intention would be to have the formula read "=IF(E5>0, AVERAGE(B5:F5)," "). Instead, I get "=IF(E5>0, AVERAGE(C5:F5)," "). So, whats its doing is increasing the column for both my start and end value by one. I want the B5 to remain static, so that it calculates the average from the beginning, but I want the later value to increase by one every time I copy it. Is there a way to lock my beginning value and still have the end value increase?

Using Excel 2007, btw.

 

[Snowknight26]

I think your example is off because if you copy

=IF(E5>0, AVERAGE(B5:E5)," ")

from E6 to F6, it should be

=IF(F5>0, AVERAGE(C5:F5)," ")

So depending on what you really want, you could do something like replace

AVERAGE(B5:F5)

with

AVERAGE(INDIRECT("B5"):F5)