Average varying block sizes in excel

Question

I need some help calculating an average of the center of a number of polygons. I have coordinates for their corners in excel. Most polygons have 4 corners, but not all.

Screenshot: Screenshot from excel

Columns E and F contains the coordinates and column D contains the condition. If the value in column D is 410.1 I need to calculate the average of columns E and F respectively, until column D reads 410.8 and then it starts over at the row just under the one with the 410.8 marker.

I have 3827 such polygons, so going through it manually is something I'd like to avoid :)

I have taken a good look at this one here: Auto calculate average over varying number values row by row

But I'm uncertain how to adapt it to my needs. I'd like to have my averages in seperate columns, so I can export it to a CSV file.

I have not tried to program in VBA or excel, so the answer might be right under my nose and I don't know it.

Best regards

Jacob

Answer 1

No need for VBA - here's the formulas. Some of these could be combined but I break it out for simplicity:

Answer 2

I can see I haven't been entirely accurate with my question. Say from Row 1 in the image provided, I can see column "D" starting with "410.1" This indicates a polygon vertex, so I keep reading down until row 4 where column "D" says "410.8", this is the last polygon point for this particular polygon. For these 4 rows I need to calculate an average for columns "E" and "F" respectively like this: avg(E1:E4) and avg(F1:F4) and put these results in two new columns.

I thought about using the block/window average function, but as far as I can tell It'll require a set window size. Not all of my polygons have 4 corners, some of them have more.

Jacob