Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Find min/max in 2 combined columns
I am trying to find the lowest, highest, and average age based on a large
list. The ages are in terms of years and months, and do not rely on birthdates. A B Years Months 1 2 10 2 5 6 3 4 8 4 2 9 Is this possible?? Thanks |
#2
|
|||
|
|||
Assuming years in A2:A30 and months in B2:B30
MAX ===================================== =SUMPRODUCT(MAX((A2:A30)+((B2:B30)/12))) will give for example 5.83 =MAX(A2:A30)&" Yr(s) & "&(12*MOD(SUMPRODUCT(MAX((A2:A30)+((B2:B30)/12))),1))&" Mths" will give for example 5 Yrs & 10 Mths MIN ===================================== =SUMPRODUCT(MIN((A2:A30)+((B2:B30)/12))) will give for example 1.25 =MIN(A2:A30)&" Yr(s) & "&(12*MOD(SUMPRODUCT(MIN((A2:A30)+((B2:B30)/12))),1))&" Mths" will give for example 1 Yr(s) & 3 Mths Assumes no blanks in your data. AVG ===================================== =AVERAGE(A2:A30)+(AVERAGE(B2:B30)/12) will give for example 3.52 =FLOOR(AVERAGE(A2:A30)+(AVERAGE(B2:B30)/12),1)&" Yr(s) & "&TEXT((12*MOD(AVERAGE(A2:A30)+(AVERAGE(B2:B30 )/12),1)),"0.0")&" Mths)" will give for example 3 Yrs & 6.2 Mths -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "Tammi" wrote in message ... I am trying to find the lowest, highest, and average age based on a large list. The ages are in terms of years and months, and do not rely on birthdates. A B Years Months 1 2 10 2 5 6 3 4 8 4 2 9 Is this possible?? Thanks |
#3
|
|||
|
|||
Using your example (in cells A1:C5):
Max =SUMPRODUCT(MAX((B2:B5)+(C2:C5)/12)) Min =SUMPRODUCT(MIN((B2:B5)+(C2:C5)/12)) Avg =SUMPRODUCT(((B2:B5)+(C2:C5)/12))/COUNT(B2:B5) Or you could use array formulas (committed with [Ctrl]+[Shift]+[Enter]): Max =MAX((B2:B5)+(C2:C5)/12) Min =MIN((B2:B5)+(C2:C5)/12) Avg =AVERAGE(((B2:B5)+(C2:C5)/12)) Does that help? -- Regards, Ron "Tammi" wrote: I am trying to find the lowest, highest, and average age based on a large list. The ages are in terms of years and months, and do not rely on birthdates. A B Years Months 1 2 10 2 5 6 3 4 8 4 2 9 Is this possible?? Thanks |
#4
|
|||
|
|||
Note though, that one set of answers gives you a numeric result and the
other gives you a text result. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I find the cell address of the 2nd largest of a set? | Excel Discussion (Misc queries) | |||
Tab Stop across Columns | Excel Worksheet Functions | |||
identify numbers which are listed in two columns. | Excel Worksheet Functions | |||
Filtering Text Data from Multiple columns | Excel Worksheet Functions | |||
combining 3 columns of same info for a pivot table | Excel Worksheet Functions |