Finding average of a range of numbers

[DOTCM]

Hi all,
I have been provided with a list of reading ages but they have been provided as ranges e.g 7year and 7 months to 7 years and 9 months is written as 7.07 - 7.09 in a cell for example. I need to find the average of the column. Is there a function and formula which could calculate this or would I need to split the columns find the average of each row and then average the columns?

Spelling age
5.04-5.05
6.04-6.05
7.05-7.06
6.04-6.05
6.11-7
8.09-8.11

#DIV/0!

Any advise would be appreciated

[Pepe Le Mokko]

Administrative note

Welcome to the forum

in your haste to solve your problem, you probably missed the yellow banner advising how to get answers faster by posting a sheet ?

Please take a moment to read it and attach a sheet accordingly.

Thanks you for helping us help you

[MrShorty]

These are text strings, and Excel does not know how to take the average of text strings. You will need to give Excel a single number that represents each range, and then average that value. One possible approach (using the lower boundary of the age range as the value).

1) In a helper column, separate out the first number (left 4 characters of the text string) and convert to a number VALUE(LEFT(text,4)). Note that I am assuming 4 characters is correct -- that there will never be an age 10 years or over.
2) Convert year.month number to a decimal year using DECIMALDE() function =DECIMALDE(VALUE(...),12). Copy/paste/fill this formula down to the bottom of the data.
3) Compute the average (in decimal years) using the AVERAGE() function =AVERAGE(helper column).
3a) If necessary, round result to the nearest month (1/12 of a year) =MROUND(AVERAGE(...),1/12)
4) If necessary, convert decimal year back into year.month using DOLLARFR() function =DOLLARFR(AVERAGE(...),12) or DOLLARFR(MROUND(...),12)

Will something like that work for you?

[DOTCM]

Just attaching example

[ChemistB]

We still need to know what you expect as a result and why? Will you just be averaging the lower boundries? or both lower and upper boundries? Or mid points?

[MrShorty]

Exactly what I proposed won't work, because there are 10+ ages in there and because you don't include the .months for whole year values. I won't try to correct it, because it was just a simple illustration of the "convert the text string age range to a number" step, and I'm not even sure if that is a suitable choice for number. I think the overall idea still stands -- you need some way to get these text strings into a suitable number. For that matter, are you required to use this for data entry? Could you replace the data entry step with something that just enters a number (whether decimal year or total month or year.month)? Sometimes this sort of thing can be "fixed" at the data entry step rather than at the formula programming step.

[ChemistB]

Try

Please Login or Register  to view this content.

This will average the midpoint of each range

[Glenn Kennedy]

What format do you want the results in? Here are two approaches...