Hello,

i have range of cells (column A). I want to count average from data from Column A in column B, but the range of average should be able to change based on my input in cell C2. It means when i input number 3 in C2, it should count average of 3 cells from column A, if i input 10 in C2, it should count average of 10 cells etc. Based on my input in cell C1 the formulas in column B would use different data set to be processed.

Basically column A has data, column B has formulas to be changed based on input of cell C2

Is somehow possible to do that in excel formulas? Or it is inevitable to use VBA?



I would appreciate if somebody has any advice. I have attached excel sheet.

Thank you

Filip

Attached Files

Excel Average.xlsx (9.5 KB, 13 views)

You do not say where the range should begin. Should it always start in A2 ?
Then =AVERAGE($A$2:INDEX($A$2:$A$12;C2))

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

It should start in A2 but the formula should affect whole range, i mean if i copy AVERAGE($A$2:INDEX($A$2:$A$12;C2)) to C5, C6, C7 etc.. it is still fixed on A2, and does not move down.. i mean the average is fixed on the 1st cell displaying average of previous values, but is not copied down does not displaying averages of followed cells.

I guess should i use two index function within average formula? So the average is counted at each followed cell in column C based on C2 value?

Try this in B2 =IF((ROW()-$C$2)>0,AVERAGE($A2:INDEX($A$2:$A$12,ROW()-$C$2)),"") and pull down as needed

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Into B2 enter the formula
=AVERAGE(OFFSET($A$1;ROW()-1;;;$C$2-1;1)
and copy down.

I would use (in B2 and copy down)

=IF(ROW(2:2)-$C$2<0;"";AVERAGE(OFFSET($A$2;ROWS($1:1);;-$C$2)))

on condition you use semicolon as argument separator.

Why use a volatile function when one can do without it?

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

@Pecoflyer

My apologies if it is me you write to.
I tried your formula and couldn't get it to work at first. Now I see it was due to the fact that you use a § instead of $ in your formula. When I corrected that it works fine, and I agree in your comment on volatile functions.

Thanks Xor for pointing this out. I had to look three times before I saw it.
Strangest thing is I just copy/pasted the formula form Openoffice ( and changed the ; to ,)
(Sometimes Linux does strange things)

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Exactly the same for me. I then used the Formula Evaluator and could see that it was somewhere in the INDEX part that something went wrong but couldn't find the error. That's the reason that I decided to use OFFSET.

thank you all for help, it works great

Also a Linux guy ( or lady)?

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

No, not a Linux guy. I just meant that I took me some time to catch the §-thing even if I from the Formula Evaluator could see that it was the INDEX-part of the formula that gave a #NAME?-error. Normally I would also have used the INDEX-function, but as I initially couldn't catch the error, I turned to OFFSET.