Dynamic Calculation for Last Column

[sg2209]

Good Afternoon,

I hope all the mentors are doing good

Need help on one of the issue where i have to calculate the Average in Cell B5 and Medican in C5 , source data is in Cell 22 to 78 and column keeps on Chanding when new data is added .

How do i calucualte

For Example :-

Date is in Coumn B22 to B78 , on next date it is in C22 to C78, then D22 to D78........it will continue until 30 columns but daily average and median is getting changed to new addition . Instead of doing it manually how do i do that with any formula or with VBA .

Please advise .


Regards
Sachin

 

[jasonb75]

Something like this will work as long as there is nothing else to the right of the data.

=AVERAGE(INDEX(22:78,0,MATCH(1e+100,22:22))

 

[sg2209]

Something like this will work as long as there is nothing else to the right of the data.

=AVERAGE(INDEX(22:78,0,MATCH(1e+100,22:22))

Thank You @jasonb75 though i am not very much smart in excel and all still learning. My last Column is U

=AVERAGE(INDEX(V22:V78,0,MATCH(E1+100,V22:V22))) Should i put the formula like this in Cell B5 , Sorry i know it seems a silly question and also will this change when any data is added ?

 

[jasonb75]

Don't put any columns in there, it will work better if it looks at the entire row. Just enter the formula exactly as I wrote it, don't change anything.

 

[sg2209]

Don't put any columns in there, it will work better if it looks at the entire row. Just enter the formula exactly as I wrote it, don't change anything.

Thanks Much . Wokring as expected