median not working

[anuradhagrewal]

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

 

[anuradhagrewal]

Even after removing the row C 767 the results are not coming

 

[Fluff]

Did you re-enter the formula using Ctrl Shift Enter, rather than just Enter?
If so you should see the formula wrapped in {}

 

[Peter_SSs]

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.

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

 

[anuradhagrewal]

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)

 

[Peter_SSs]

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
	C	D	E	F
1
2	Age on 31-Mar-24	Current Location	Subject Taught	ECTC
3	24	MP	ACCOUNTS	83,333
4	23	NCR	ENGLISH	75,000
5	23	DELHI	PE	45,000
6	24	ORISSA	ACCOUNTS	50,000
7	24	MP	MATHS	45,000
8	24	DELHI	ENGLISH	15,000
9	24	NCR	MATHS	40,000
10	24	DELHI	PE	40,000
11	22	GGN	MATHS	10,000
12	21	GGN	PE	25,000
13
MAIN

anuradhagrewal File_2.xlsx
	D	E	F	G	H	I	J	K
1
2	MEDIAN SALARY BY AGE			MEDIAN SALARY BY LOCATION			MEDIAN SALARY BY SUBJECTS TAUGHT
3	AGE	SALARY		LOCATION	SALARY		SUBJECT	SALARY
4	21	25000		MP	64166.66667		ACCOUNTS	66666.66667
5	22	10000		NCR	57500		ENGLISH	45000
6	23	60000		DELHI	40000		PE	40000
7	24	42500		ORISSA	50000		MATHS	40000
8				GGN	17500
9
MEDIAN

Cell Formulas
Range	Formula
E4:E7	E4	=MEDIAN(IF(MAIN!C$3:C$12=D4,MAIN!F$3:F$12))
K4:K7	K4	=MEDIAN(IF(MAIN!E$3:E$12=J4,MAIN!F$3:F$12))
H4:H8	H4	=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

 

[anuradhagrewal]

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

 

[Peter_SSs]

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"