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

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

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

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 :-)
----------------------------------------------------------------------------