Averaging Numbers when 2 numbers in one cell
 

I am using an Excel spreadsheet with 2 numbers in every cell separated my a
comma and a space (45, 55) and want to calculate the average for only the
left numbers across a variety of cells [(45,55) (55,55) (65,85)]. Here the
averages I am looking for would be 55 for the left set of numbers and 65 for
the right set. I was wondering if there is any way to do this automatically
in Excel instead of going through and doing them all manually. Any help you
can provide would be most appreciated.

Hi

will this work?
=AVERAGE(VALUE(RIGHT(A1,2)),VALUE(LEFT(C1,2)))

this assumes that all your numbers are two digits, if they are off varying
length then try
=AVERAGE(VALUE(RIGHT(A1,LEN(A1)-FIND("
",A1,1))),VALUE(LEFT(C1,FIND(",",C1,1)-1)))

Cheers
JulieD

"Ourania" wrote in message
...
I am using an Excel spreadsheet with 2 numbers in every cell separated my a
comma and a space (45, 55) and want to calculate the average for only the
left numbers across a variety of cells [(45,55) (55,55) (65,85)]. Here
the
averages I am looking for would be 55 for the left set of numbers and 65
for
the right set. I was wondering if there is any way to do this
automatically
in Excel instead of going through and doing them all manually. Any help
you
can provide would be most appreciated.

For the left batch:

=AVERAGE(--LEFT(A1:A3,FIND(",",A1:A3)-1))

for the right:

=AVERAGE(--MID(A1:A3, FIND(",",A1:A3)+1,255))

both entered with CTRL Shift Enter.


"Ourania" wrote in message
...
I am using an Excel spreadsheet with 2 numbers in every cell separated my
a
comma and a space (45, 55) and want to calculate the average for only the
left numbers across a variety of cells [(45,55) (55,55) (65,85)]. Here
the
averages I am looking for would be 55 for the left set of numbers and 65
for
the right set. I was wondering if there is any way to do this
automatically
in Excel instead of going through and doing them all manually. Any help
you
can provide would be most appreciated.

=AVERAGE(LEFT(Range&",",SEARCH(",",Range&",")-1)+0)

which must be confirmed with control+shift+enter instead of just with enter.

Ourania wrote:
I am using an Excel spreadsheet with 2 numbers in every cell separated my a
comma and a space (45, 55) and want to calculate the average for only the
left numbers across a variety of cells [(45,55) (55,55) (65,85)]. Here the
averages I am looking for would be 55 for the left set of numbers and 65 for
the right set. I was wondering if there is any way to do this automatically
in Excel instead of going through and doing them all manually. Any help you
can provide would be most appreciated.

Ourania wrote...
I am using an Excel spreadsheet with 2 numbers in every cell separated
my a
comma and a space (45, 55) and want to calculate the average for only
the
left numbers across a variety of cells [(45,55) (55,55) (65,85)].
Here the
averages I am looking for would be 55 for the left set of numbers and
65 for
the right set. I was wondering if there is any way to do this
automatically
in Excel instead of going through and doing them all manually. Any
help you
can provide would be most appreciated.

These are (x, y) pairs? Do they also include the parentheses? Here are
*array* formulas that can deal with parentheses whether they're
included or not.

x values in Rng:
=AVERAGE(--SUBSTITUTE(LEFT(Rng,FIND(",",Rng)-1),"(",""))

y values in Rng:
=AVERAGE(--SUBSTITUTE(MID(Rng,FIND(",",Rng)+1,256),")",""))

JulieD wrote...
....
=AVERAGE(VALUE(RIGHT(A1,LEN(A1)-FIND(" ",A1,1))),
VALUE(LEFT(C1,FIND(",",C1,1)-1)))
....

RIGHT(x,LEN(x)-FIND(y,x)) is an awkward way to return the characters to
the right of a character position measured from the left side of a
string. Since MID's 3rd argument is implicitly truncated to the
remaining characters in its 1st argument after its 2nd argument,
MID(x,FIND(y,x)+1,32768) would give the same result without the LEN
call. Maybe not a big deal in this case, but using
RIGHT(x,LEN(x)-FIND(y,x)) gets messy when x is itself an expression
rather than a constant string or simple range reference.

Aladin Akyurek wrote...
=AVERAGE(LEFT(Range&",",SEARCH(",",Range&",")-1)+0)
....

Good to append the comma to Range inside the SEARCH call, useless to do
so in LEFT's 1st argument.

I used the following formulars and for the x value I got a 45 when it should
be a 55 and for the y value I got a 55 when it should be a 65. What am I
doing wrong?

x values in Rng:
=AVERAGE(--SUBSTITUTE(LEFT(Rng,FIND(",",Rng)-1),"(",""))

y values in Rng:
=AVERAGE(--SUBSTITUTE(MID(Rng,FIND(",",Rng)+1,256),")",""))

" wrote:

Ourania wrote...
I am using an Excel spreadsheet with 2 numbers in every cell separated
my a
comma and a space (45, 55) and want to calculate the average for only
the
left numbers across a variety of cells [(45,55) (55,55) (65,85)].
Here the
averages I am looking for would be 55 for the left set of numbers and
65 for
the right set. I was wondering if there is any way to do this
automatically
in Excel instead of going through and doing them all manually. Any
help you
can provide would be most appreciated.

These are (x, y) pairs? Do they also include the parentheses? Here are
*array* formulas that can deal with parentheses whether they're
included or not.

x values in Rng:
=AVERAGE(--SUBSTITUTE(LEFT(Rng,FIND(",",Rng)-1),"(",""))

y values in Rng:
=AVERAGE(--SUBSTITUTE(MID(Rng,FIND(",",Rng)+1,256),")",""))

Follow the instructions on entering the formulas given in my post, Aladin's,
and referenced in Harlan's (look for the *emphasis*).


"Ourania" wrote in message
...
I used the following formulars and for the x value I got a 45 when it
should
be a 55 and for the y value I got a 55 when it should be a 65. What am I
doing wrong?

x values in Rng:
=AVERAGE(--SUBSTITUTE(LEFT(Rng,FIND(",",Rng)-1),"(",""))

y values in Rng:
=AVERAGE(--SUBSTITUTE(MID(Rng,FIND(",",Rng)+1,256),")",""))

" wrote:

Ourania wrote...
I am using an Excel spreadsheet with 2 numbers in every cell separated
my a
comma and a space (45, 55) and want to calculate the average for only
the
left numbers across a variety of cells [(45,55) (55,55) (65,85)].
Here the
averages I am looking for would be 55 for the left set of numbers and
65 for
the right set. I was wondering if there is any way to do this
automatically
in Excel instead of going through and doing them all manually. Any
help you
can provide would be most appreciated.

These are (x, y) pairs? Do they also include the parentheses? Here are
*array* formulas that can deal with parentheses whether they're
included or not.

x values in Rng:
=AVERAGE(--SUBSTITUTE(LEFT(Rng,FIND(",",Rng)-1),"(",""))

y values in Rng:
=AVERAGE(--SUBSTITUTE(MID(Rng,FIND(",",Rng)+1,256),")",""))