MIN and MAX across columns
 

Hi folks

I have gender in one column and age in second column. I need to find minimum
age for male patients, for female patients and maximum age as well. I tried
with IF function but it doesn't work

=IF(O3:O15="M";MIN(P3:P15)) in Q3 cell

O P Q
1 GENDER AGE MIN(M) MIN(F) MAX(M) MAX(F)
2
3 M 45
4 F 63
5 M 83
6 M 63
7 M 84
8 F 49
9 F 55
10 F 62
11 M 73
12 F 42
13 M 54
14 F 64
15 M 73

I think I'm on the right path but I need your help. Thanks in advance

MIN and MAX across columns
 

=MIN(IF($A$3:$A$15="M",$B$3:$B$15))

etc.

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Bark!" wrote in message ...
Hi folks

I have gender in one column and age in second column. I need to find
minimum age for male patients, for female patients and maximum age as
well. I tried with IF function but it doesn't work

=IF(O3:O15="M";MIN(P3:P15)) in Q3 cell

O P Q
1 GENDER AGE MIN(M) MIN(F) MAX(M) MAX(F)
2
3 M 45
4 F 63
5 M 83
6 M 63
7 M 84
8 F 49
9 F 55
10 F 62
11 M 73
12 F 42
13 M 54
14 F 64
15 M 73

I think I'm on the right path but I need your help. Thanks in advance

MIN and MAX across columns
 

=MIN(IF(O3:O15="M",P3:P15))

entered with ctrl + shift & enter (this is important)


--

Regards,

Peo Sjoblom




"Bark!" wrote in message ...
Hi folks

I have gender in one column and age in second column. I need to find
minimum age for male patients, for female patients and maximum age as
well. I tried with IF function but it doesn't work

=IF(O3:O15="M";MIN(P3:P15)) in Q3 cell

O P Q
1 GENDER AGE MIN(M) MIN(F) MAX(M) MAX(F)
2
3 M 45
4 F 63
5 M 83
6 M 63
7 M 84
8 F 49
9 F 55
10 F 62
11 M 73
12 F 42
13 M 54
14 F 64
15 M 73

I think I'm on the right path but I need your help. Thanks in advance

MIN and MAX across columns
 

Your formula works when entered as an array formula - use CTRL+SHIFT+ENTER
to conform the formula.
(I assume your county uses ; for argument separation)
It this does not work make sure you have single letters in O; for example
does =O3="M" return TRUE?
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Bark!" wrote in message ...
Hi folks

I have gender in one column and age in second column. I need to find
minimum age for male patients, for female patients and maximum age as
well. I tried with IF function but it doesn't work

=IF(O3:O15="M";MIN(P3:P15)) in Q3 cell

O P Q
1 GENDER AGE MIN(M) MIN(F) MAX(M) MAX(F)
2
3 M 45
4 F 63
5 M 83
6 M 63
7 M 84
8 F 49
9 F 55
10 F 62
11 M 73
12 F 42
13 M 54
14 F 64
15 M 73

I think I'm on the right path but I need your help. Thanks in advance

MIN and MAX across columns
 

It doesn't work regardless if it is entered with ctrl + shift & enter



--

Regards,

Peo Sjoblom





"Bernard Liengme" wrote in message
...
Your formula works when entered as an array formula - use CTRL+SHIFT+ENTER
to conform the formula.
(I assume your county uses ; for argument separation)
It this does not work make sure you have single letters in O; for example
does =O3="M" return TRUE?
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Bark!" wrote in message
...
Hi folks

I have gender in one column and age in second column. I need to find
minimum age for male patients, for female patients and maximum age as
well. I tried with IF function but it doesn't work

=IF(O3:O15="M";MIN(P3:P15)) in Q3 cell

O P Q
1 GENDER AGE MIN(M) MIN(F) MAX(M) MAX(F)
2
3 M 45
4 F 63
5 M 83
6 M 63
7 M 84
8 F 49
9 F 55
10 F 62
11 M 73
12 F 42
13 M 54
14 F 64
15 M 73

I think I'm on the right path but I need your help. Thanks in advance

MIN and MAX across columns
 

Should have tested!
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Peo Sjoblom" wrote in message
...
It doesn't work regardless if it is entered with ctrl + shift & enter



--

Regards,

Peo Sjoblom





"Bernard Liengme" wrote in message
...
Your formula works when entered as an array formula - use
CTRL+SHIFT+ENTER to conform the formula.
(I assume your county uses ; for argument separation)
It this does not work make sure you have single letters in O; for example
does =O3="M" return TRUE?
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Bark!" wrote in message
...
Hi folks

I have gender in one column and age in second column. I need to find
minimum age for male patients, for female patients and maximum age as
well. I tried with IF function but it doesn't work

=IF(O3:O15="M";MIN(P3:P15)) in Q3 cell

O P Q
1 GENDER AGE MIN(M) MIN(F) MAX(M) MAX(F)
2
3 M 45
4 F 63
5 M 83
6 M 63
7 M 84
8 F 49
9 F 55
10 F 62
11 M 73
12 F 42
13 M 54
14 F 64
15 M 73

I think I'm on the right path but I need your help. Thanks in advance

MIN and MAX across columns
 

"Bernard Liengme" wrote in message
...
Your formula works when entered as an array formula - use CTRL+SHIFT+ENTER
to conform the formula.
(I assume your county uses ; for argument separation)
It this does not work make sure you have single letters in O; for example
does =O3="M" return TRUE?
best wishes

OMG thnak you ppl

yes ; is the separator in my country