A Range that'll Change

[pwillia]

Hello everybody,

Here is my original code, which goes down column F, from F4 on-wards. It averages 5 cells at a time, the result of which it places in column G, from G4 on-wards.
It uses relative values, so cell G14 will have the value of F14:18/5 and so on.

Range("g4", "g250").Formula = "=SUM(F4:F8)/5"

I have tested this with a fixed amount of data up to cell F255, meaning the formula can only calculate up to G250 as it needs the 5 cells below it in the F column to calculate a corresponding G value- and it has worked fine.


I would now like to apply the above formula to variable amounts of data, meaning that the F column's data will vary in length.

The G column needs to calculate for how ever much data there is in the F column minus 5 cells.

So if the F column populates from F4:F1000, I would need the G column to produce calculations in the range G4:G995.



So how can I make this formula work for variable amounts of data?

My thoughts so far have been to use an "xldown" function to select how many cells have data in column F (which will always hold contiguous data), thus stopping when there is an empty cell below. Then to deduct 5 from that value, and use "cell.offset" to place the end of the G range in the formula. Am I thinking about this correctly or is this fundamentally incorrect?


Any help is greatly appreciated and I would like to thank you for your time in reading my question.

Pete.

 

[Norie]

Pete

Try this.

LastRow = Range("F" & Rows.Count).End(xlUp).Row-3

Range("G4:G" & LastRow).Formula = "=SUM(F4:F8)/5"

Note, this would go down to 997 if the last row in F is 1000, the formula in G996 would b =SUM(F997:F1000)/5,

 

[pwillia]

Pete

Try this.

LastRow = Range("F" & Rows.Count).End(xlUp).Row-3

Range("G4:G" & LastRow).Formula = "=SUM(F4:F8)/5"

Note, this would go down to 997 if the last row in F is 1000, the formula in G996 would b =SUM(F997:F1000)/5,

Wow, that was quick!
Yes, I have changed that slightly to Row-4, giving me the selection of 5 cells I needed. Also I realised I wrote that I need the 5 cells below G, when in fact I needed 1 cell adjacent and 4 below (in F column), so my code renders:

G996 =SUM(F996:F1000)/5

Thank you so much, it works perfectly! When I read it, it makes perfect sense, I just didn't have enough experience to put it together.

Thanks again Norie I really appreciate it.