Use cell value as a variable counter (e.g. i, j)

Dear all,

I want to calculate simple median of the area of cells. The problem is, the number of cells per slide don't change linearly. For example in the first slide (shown in yellow), there are five cells with respective areas in column B. In the next slide, there are four cells (blue), etc. I have created two index columns (Index1, Index2) that mark the start and end of each slides. Now, I want to calculate the median of area in each slide as F2=MEDIAN(B(C2):B(D2)), F3= MEDIAN(B(C3):B(D3)), etc. However, these type of referencing is obviously not defined by Excel and creates error. Anybody knows how to get around this?

Cheers,
Shahed

Excel problem.jpg

Perhaps = MEDIAN(INDEX($B$2:$B$100,$C2):INDEX($B$2:$B$100,$D2))

Adapt ranges and pull down as required

(Also possible with the INDIRECT function, but that is volatile

was going to use offset but volatile so yours is better Pepe

but perhaps a slight amendment
=MEDIAN(INDEX($B$1:$B$100,$C2):INDEX($B$1:$B$100,$D2))

if we had had an answer it would be easier to confirm!

I was just about to post that there was a slight miscalculation with the first formula. It worked for the first row, but not the other ones.

Attachment 630678

Yours work like a charm!

Attachment 630679

Cheers to both of you! It saved my day

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.