Average of a dynamic range (starting from the4 bottom!)

[Ealglez]

Dear All,

I am new in VBA and I would like to do the following for my master w/s: I have a trend of time vs. variable XD (the length of this trend will vary - can be a total time of 15 hours, 20 hrs, 5hours etc. with variable intervals as well).

I would like to calculate the average of the variable XD Only for the last 2 hours of the trend, i.e. if the trend if from 0 to 15 hours, I would like to calculate the average of XD from t = 13 hours to t = 15 hours. For case 1, trend data is in A6:B102 (headers in A6:B6).

As mentioned above, this trend will change for each case I have and therefore a definition of a dynamic range is necessary ( I guess!). My logic is to create a macro able to count until the last row, read the time in column A (15 hours in the example), locate the row Ax with time (15-2) hours and then average the corresponding values in Bx:B102 cells. Alos, please not that time should be rounded to the 2nd decimal (ex. t = 15.00032 and t= 13.000456 in trend data).

Thank you very much!
E

 

[RickXL]

Hi,

If you use a Table (Menu: Insert-->Table) that will take care of the dynamic bit. The table will expand as data is added.

I put some data labelled "a" and "b" in the first two columns then converted it to a table (called Table1 by default).

My formula became: =AVERAGEIF(Table1[a],">="&(MAX(Table1[a])-2),Table1)