We'd like to remind Forumites to please avoid political debate on the Forum... Read More »
We're aware that some users are experiencing technical issues which the team are working to resolve. See the Community Noticeboard for more info. Thank you for your patience.
📨 Have you signed up to the Forum's new Email Digest yet? Get a selection of trending threads sent straight to your inbox daily, weekly or monthly!
Excel Query
Options

choyaa
Posts: 226 Forumite
in Techie Stuff
Hi All,
This code has been giving me problems and I thought I'd try again on here to shed some light on it as the results are not what was expected. What I need to do is search column B (Peer Group) for matching values and compare these with what's in column AJ (2015 Tcomp) and return the median value. Below is the code that is currently being used but the values returned are inaccurate. At present I am not allowed to change the below code but can anyone advise as to what this code means and why it fails? Obviously when I drag down the formula B3 changes to B4 etc etc.
=MEDIAN(IF(peer=$B3,IF(tcomp>0, tcomp)))
This code has been giving me problems and I thought I'd try again on here to shed some light on it as the results are not what was expected. What I need to do is search column B (Peer Group) for matching values and compare these with what's in column AJ (2015 Tcomp) and return the median value. Below is the code that is currently being used but the values returned are inaccurate. At present I am not allowed to change the below code but can anyone advise as to what this code means and why it fails? Obviously when I drag down the formula B3 changes to B4 etc etc.
=MEDIAN(IF(peer=$B3,IF(tcomp>0, tcomp)))
0
Comments
-
I presume 'tcomp' is a named range. So it seems to me that if 'peer' is equal to the contents of the selected cell (B3 in this case), and if 'tcomp' is greater than zero, then it returns the median value of the 'tcomp' range, otherwise it returns zero.
I have a strong sense of deja vu on this, but I've had that before.0 -
I think your employer should be paying for your training here rather than expecting you to rely on internet forums everytime you have an excel query (which seems to be a lot)
http://www.pitman-training.com/courses/microsoft-excel-expertdroopsnoot wrote: »I have a strong sense of deja vu on this, but I've had that before.
There is a fault in the matrix
https://forums.moneysavingexpert.com/discussion/55140970
This discussion has been closed.
Confirm your email address to Create Threads and Reply

Categories
- All Categories
- 350.9K Banking & Borrowing
- 253.1K Reduce Debt & Boost Income
- 453.5K Spending & Discounts
- 243.9K Work, Benefits & Business
- 598.7K Mortgages, Homes & Bills
- 176.9K Life & Family
- 257.1K Travel & Transport
- 1.5M Hobbies & Leisure
- 16.1K Discuss & Feedback
- 37.6K Read-Only Boards