median not working

anuradhagrewal

Board Regular
Joined
Dec 3, 2020
Messages
87
Office Version
  1. 2010
Platform
  1. Windows
Hi Guys
I need your help
The file is here
I am trying to figure out the median pay with respect to Age location and subject taught (MEDIAN worksheet)
Can you guys please help me out
The file is attached here.


Regards

Anu
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Did you re-enter the formula using Ctrl Shift Enter, rather than just Enter?
If so you should see the formula wrapped in {}
 
Upvote 0
Just adding to what @Fluff has said: This is what you should see in the formula bar if the formula in cell E4 of 'MEDIAN' sheet is entered correctly with Ctrl+Shift+Enter, not just Enter.

1710109530027.png


However, also be clear that the curly braces shown under the red lines are not typed into the formula, they just appear there after the formula
=MEDIAN(IF(MAIN!C$3:C$878=D4,MAIN!F$3:F$878))
is entered in the cell and confirmed with Ctrl+Shift+Enter
 
Upvote 0
Just adding to what @Fluff has said: This is what you should see in the formula bar if the formula in cell E4 of 'MEDIAN' sheet is entered correctly with Ctrl+Shift+Enter, not just Enter.

View attachment 108147

However, also be clear that the curly braces shown under the red lines are not typed into the formula, they just appear there after the formula
=MEDIAN(IF(MAIN!C$3:C$878=D4,MAIN!F$3:F$878))
is entered in the cell and confirmed with Ctrl+Shift+Enter
can you please email me the update file on my id
(email removed)
 
Last edited by a moderator:
Upvote 0
Sorry, taking the threads off-forum is not permitted by the Forum Rules see #4.
As a result, I have removed your email address from the post.


I believe that we have given you all the details that you need to get the results. I suspect that the issue is that you are not confirming the formula correctly. That is after entering the formula text in the cell, hold down the Ctrl key and the Shift key and while holding those two keys down press the Enter key.

What I can do is provide a small example as below (I have changed the data)

anuradhagrewal File_2.xlsx
CDEF
1
2Age on 31-Mar-24Current LocationSubject Taught ECTC
324MPACCOUNTS83,333
423NCRENGLISH75,000
523DELHIPE45,000
624ORISSAACCOUNTS50,000
724MPMATHS45,000
824DELHIENGLISH15,000
924NCRMATHS40,000
1024DELHIPE40,000
1122GGNMATHS10,000
1221GGNPE25,000
13
MAIN


anuradhagrewal File_2.xlsx
DEFGHIJK
1
2MEDIAN SALARY BY AGEMEDIAN SALARY BY LOCATIONMEDIAN SALARY BY SUBJECTS TAUGHT
3AGESALARYLOCATIONSALARYSUBJECTSALARY
42125000MP64166.66667ACCOUNTS66666.66667
52210000NCR57500ENGLISH45000
62360000DELHI40000PE40000
72442500ORISSA50000MATHS40000
8GGN17500
9
MEDIAN
Cell Formulas
RangeFormula
E4:E7E4=MEDIAN(IF(MAIN!C$3:C$12=D4,MAIN!F$3:F$12))
K4:K7K4=MEDIAN(IF(MAIN!E$3:E$12=J4,MAIN!F$3:F$12))
H4:H8H4=MEDIAN(IF(MAIN!D$3:D$12=G4,MAIN!F$3:F$12))
Press CTRL+SHIFT+ENTER to enter array formulas.


The curly braces do not show up in a mini sheet but they are there in my worksheet. Example with H4 selected I see this

1710134403360.png
 
Upvote 0
It worked sir
But why do I have to hold the Ctrl key and the Shift key and while holding those two keys down press the Enter key.
Is this only for Median or what is its significane
 
Upvote 0
It worked sir
Glad you got there in the end.

But why do I have to hold the Ctrl key and the Shift key and while holding those two keys down press the Enter key.
Is this only for Median or what is its significane
It is not related to the Median function or any other function. It is because the formula is an array formula. You need to do some research about array formulas. Here is one place to start. In particular, in relation to this thread, look down to the section titled "Single Cell Array Formula"
 
Upvote 0

Forum statistics

Threads
1,223,888
Messages
6,175,219
Members
452,619
Latest member
Shiv1198

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top