Average cells within a range updates automatically, based on row values

0
votes

I have attached a sample file below. Can you help?

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

The range of the average formula in Cell B1, should update to show B2:B4... etc and continue to automatically update as new rows are added and additional numbers are added (1.4 in cell A5, average range updates to B2:B5, 1.5 in A6, average range updates to B2:B6 and so on...)

Thanks in advance for your help

Sample

1
excel
Sample input and output are welcome. - Sangbok Lee
Please note that "I need a little support" isn't really a question. Can you be more specific? Please also show what you've tried so far. - EJoshuaS - Stand with Ukraine
@EJoshuaS appreciate its not a question, but i was trying trying to convey i'm not a complete novice. Will try harder in future. - McGooks
@SangbokLee i have added a link to the book in the request for help. Basically i'm looking for the formula in cell B1 to recalculate the range B2:B3 if a new row is inserted and 1.3 is added in cell A4. - McGooks
Edit your question according to the sample file, then I'll take a look at it. - Sangbok Lee

1 Answers

0
votes

Okay, finally I got what you mean. But your sheet looks not very efficient. Why not set A1 to 1, and A2 to =A1+0.1, and so on? For now A column has very long formulae which is hard to read.

Anyway... to use averageif() on 1.1, 2.1, etc, it'll be easier to make A column to text. So add another column(say C) and set C1 to =text(a1, "#") and drag down. Now that C1 is text, you can use averageif() with wildcard(*). set B1 like this:

=AVERAGEIF(C2:C99, C1&"*", B2:B99)

Of course, you should copy B1 and paste onto B4 and so on manually. Sample Excel file is here.