| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional maximum
Hi. I have a sheet showing the number of classrooms for a range of schools.
It has school names in column A and the number of the classroom in column B, eg. School Classroom number Sydney Primary School 1 Sydney Primary School 2 Sydney Primary School 3 Sydney Primary School 4 London School 1 London School 2 London School 3 New York School 1 New York School 2 New York School 3 New York School 99 New York School 99 I want to have a column that shows the number of classrooms for each school. Sydney=4 and London=3 for example. Further complicating matters is that some schools have a 99 for classroom number after the initial number sequence. This represents an off location room. There may be multiple 99's. I do not want to count these values. So New York above would be 3. Have tried using max functions but with no success. Any suggestions? Thanks in advance. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Conditional maximum
One way...
Try this array formula** : =MAX(IF((A2:A100="New York School")*(B2:B100<99),B2:B100)) Or, use a cell to hold the school name: D2 = Sydney Primary School =MAX(IF((A2:A100=D2)*(B2:B100<99),B2:B100)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Jai" wrote in message ... Hi. I have a sheet showing the number of classrooms for a range of schools. It has school names in column A and the number of the classroom in column B, eg. School Classroom number Sydney Primary School 1 Sydney Primary School 2 Sydney Primary School 3 Sydney Primary School 4 London School 1 London School 2 London School 3 New York School 1 New York School 2 New York School 3 New York School 99 New York School 99 I want to have a column that shows the number of classrooms for each school. Sydney=4 and London=3 for example. Further complicating matters is that some schools have a 99 for classroom number after the initial number sequence. This represents an off location room. There may be multiple 99's. I do not want to count these values. So New York above would be 3. Have tried using max functions but with no success. Any suggestions? Thanks in advance. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Conditional maximum
Didn't work when I tried to use it as an array over the whole range but did
when I used it as an array on each cell. Thanks "T. Valko" wrote: One way... Try this array formula** : =MAX(IF((A2:A100="New York School")*(B2:B100<99),B2:B100)) Or, use a cell to hold the school name: D2 = Sydney Primary School =MAX(IF((A2:A100=D2)*(B2:B100<99),B2:B100)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Jai" wrote in message ... Hi. I have a sheet showing the number of classrooms for a range of schools. It has school names in column A and the number of the classroom in column B, eg. School Classroom number Sydney Primary School 1 Sydney Primary School 2 Sydney Primary School 3 Sydney Primary School 4 London School 1 London School 2 London School 3 New York School 1 New York School 2 New York School 3 New York School 99 New York School 99 I want to have a column that shows the number of classrooms for each school. Sydney=4 and London=3 for example. Further complicating matters is that some schools have a 99 for classroom number after the initial number sequence. This represents an off location room. There may be multiple 99's. I do not want to count these values. So New York above would be 3. Have tried using max functions but with no success. Any suggestions? Thanks in advance. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Conditional maximum
Didn't work when I tried to use it as an array over
the whole range but did when I used it as an array on each cell. Not sure what that means but if by "whole range" you mean an entire column then no, it wouldn't work because an array formula can't reference an entire column (unless you're using Excel 2007). -- Biff Microsoft Excel MVP "Jai" wrote in message ... Didn't work when I tried to use it as an array over the whole range but did when I used it as an array on each cell. Thanks "T. Valko" wrote: One way... Try this array formula** : =MAX(IF((A2:A100="New York School")*(B2:B100<99),B2:B100)) Or, use a cell to hold the school name: D2 = Sydney Primary School =MAX(IF((A2:A100=D2)*(B2:B100<99),B2:B100)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Jai" wrote in message ... Hi. I have a sheet showing the number of classrooms for a range of schools. It has school names in column A and the number of the classroom in column B, eg. School Classroom number Sydney Primary School 1 Sydney Primary School 2 Sydney Primary School 3 Sydney Primary School 4 London School 1 London School 2 London School 3 New York School 1 New York School 2 New York School 3 New York School 99 New York School 99 I want to have a column that shows the number of classrooms for each school. Sydney=4 and London=3 for example. Further complicating matters is that some schools have a 99 for classroom number after the initial number sequence. This represents an off location room. There may be multiple 99's. I do not want to count these values. So New York above would be 3. Have tried using max functions but with no success. Any suggestions? Thanks in advance. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Conditional formatting data based on the maximum in the set | Excel Worksheet Functions | |||
| Maximum | Excel Worksheet Functions | |||
| I need conditional formatting to apply to maximum % | Excel Worksheet Functions | |||
| Maximum If Help | Excel Worksheet Functions | |||
| MAXIMUM VALUE | Excel Worksheet Functions | |||
Linear Mode
