Median of a variable range defined by VLookup

**jonesgary** · 02-18-2020, 11:42 AM

Hi,

I am new to this forum. Having been an Excel user for many years I have decided to dig deeper into the functionality etc not least as I have requirement that Excel perfectly fits.

However, I am stumped by one requirement and have attached a sheet as an example.

Basically, I have a range of data over four columns - column 1 is the row number, column 2 the company name, column 3 the number of offices that company has and column 4 is the cumulative number of offices. This is referenced by a VLOOKUP calculation which uses the number from a separate field for the look_up value and this number is not fixed.

((VLOOKUP(C3,I3:L52,3,TRUE)))

Initially, I was taking the result of the above and then dividing it to return the average number offices for the range, ((VLOOKUP(C3,I3:L52,3,TRUE))/C3).

However, I would like to use MEDIAN as it is more representative. I then tried MEDIAN(((VLOOKUP(C3,I3:L52,3,TRUE)))). This returns a number each time the variable number in cell C3 is changed.

When I have manually checked this MEDIAN in a separate cell the results are different.

I have no doubt I am not using MEDIAN correctly but have a mental block on how to acheive my objective.

All and any help appreciated.

Thanks.

Best Wishes

Gary

**Kaper** · 02-18-2020, 12:32 PM

I'm not sure what you want to achieve. Let me first explain how VLOOKUP works.
You do VLOOKUP(C3,I3:L52,3,TRUE) Lets start from 4th argument of this function. Its TRUE, so:
- you need first column of the 3:L52 to be sorted in ascending order (it is) and Vlookup will search this column top-down for last value which is not greater than 1st argument (C3 - which in sample file hav value of 200). So it will find last row if column I, containing in I52 value of 50 (so still less than 200). And then VLOOKUP will return value from 3rd (as you used third argument equal 3) column in range from 2nd argument, so value from cell K52.

So the median of the range is calculated exactly as you did in C6
the average will be calculated properly =AVERAGE(K3:K52) and it is far away from ((VLOOKUP(C3,I3:L52,3,TRUE))/C3)

In my opinion - you simply don't need VLOOKUP here.

If that "unblocks" you - perfect. If not - please explain again, what you want to achieve. May be with smaller sample file - some 10-20 rows of data are usually more than enough (and it's easy to mock expected result manually for such small set).

**jonesgary** · 02-18-2020, 12:41 PM

Hi Kaper

Thanks for your reply. I will look at the average function and of course using True will cause the difference.

Best

G

**Kaper** · 02-18-2020, 12:45 PM

Just let me emphasize it: VLOOKUP returns only one value (or an error

). It's a value from specific place in a range provided as second argument to the function. So Median(VLOOKUP(....)) or AVERAGE(VLOOKUP(....)) or MAX(VLOOKUP(....)) etc does not make any difference it's still the same single value.

Median of a variable range defined by VLookup

LinkBack

Thread Tools

Rate This Thread

Display

Median of a variable range defined by VLookup

Re: Median of a variable range defined by VLookup

Re: Median of a variable range defined by VLookup

Re: Median of a variable range defined by VLookup

Thread Information

Users Browsing this Thread

Similar Threads

How to set Range as a User-Defined variable

Clearing a Range of Cells defined by a Variable Range

Median Indirect: Find median in range and bring back adjacent cells

Macro to Insert defined integer into range defined by variable criteria

[SOLVED] Using cell value to define a range (for a variable defined as a range)

Calculating median on a filtered range. custom defined function. #NAME error in cell

[SOLVED] VBA/Vlookup with workbook defined in a variable

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions