Median for a specific number of nonblanks

[Mc922]

Hi all,

Scenario: I've sorted by date in column A. Now I'm trying to find the easiest way to get a median from the first 50 nonblank rows in column B.

The catch: I don't want to delete the rows with blanks in column B because I need the other columns in these rows for other calculations. I'd also prefer not to change the current sort order if possible.

Any ideas?

 

[energman58]

There is probably a better way to do this but you could add a helper column somewhere which basically counts the number of non-blank values up to and including that row maybe using Counta($B$1:B1) copied down

Then your formula would be:

=MEDIAN(OFFSET(B1,0,0,MATCH(50,$C:$C,0)))

Assuming your data start at B1 in each case and that C is your helper column starting at C1

 

[Peter_SSs]

Even though this is looking for the last (not first) x values and is arranged in rows (not columns), it should give you a good starting point. Scroll up to see the early part of the thread.

 

[Mc922]

Thanks for the helpful replies. Offset got me on the right track, I didn't know much about it until now. Ended up using a VBA sub to count down the number of rows until it reached 50 nonblank values and wrote that row count to cell. Then in the next cell over I used the Median / Offset formula with the 3rd parameter of 'height' pointing to the row count cell. VBA seemed overkill for what I wanted but then I really didn't want to mess with a helper column in this case and trying to get that to work etc. Bottom line, it works, thanks again for the suggestions.