Dynamic Range in AVERAGE formula

[mcgooks]

Hi Mr Excel,

I really need some help, i have been trying to find a workable solution for a problem i have calculating averages.

I'm looking for a formula to show the calculated average in cell B2 of the included sample, were the range (currently B3:B4) updates automatically when a new row is inserted at row 5 and the value 1.3 is inserted in cell A5.

If the user inserts 1.3 (as mentioned about the range of the average formula in Cell B2, should update to show B3:B5... etc and continue to automatically update as new rows are added and additional numbers are added (1.4 in cell A6, average range updates to B3:B6, 1.5 in A7, average range updates to B3:B7 and so on...). I really want the range to update as long as the prefix in column A, starts with the same whole integer.

I plan to reuse the formula in Cell B5 in the example below for all of the 2.x prefixes etc.


[TABLE="class: grid, width: 200, align: left"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Ref[/TD]
[TD]% Complete[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1.0[/TD]
[TD]20%[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1.1[/TD]
[TD]40%[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1.2[/TD]
[TD]0%[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]2.0[/TD]
[TD]0%[/TD]
[/TR]
</tbody>[/TABLE]









there is a sample excel version here - any help here would be greatly appreciated. I have been trying to figure out a way to do this for a while.

 

[mcgooks]

I should have also detailed, that i have set up the formula using a basic AVERAGE(B3:B4), however, more often that not the user inserts a new line after row 4 and inserts 1.3, the often forget to update the formula for the average, hence me looking to create a formula as a workaround automate the range updating