Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
to convert columns to rows having mulit independent group columns | Excel Worksheet Functions | |||
Combine multiple columns into two long columns, Repeating rows in first column | Excel Discussion (Misc queries) | |||
Combine multiple columns into two long columns, Repeating rows in first column | Excel Discussion (Misc queries) | |||
Create stacked columns chart but with 2 columns for each x axis? | Charts and Charting in Excel | |||
Pivot Table Creating New Columns that Subtract Two Existing Columns | Excel Discussion (Misc queries) |