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" <Ourania@discussions.microsoft.com> wrote in message
news:5DE844AE-07F6-41D1-B98C-E2CF94BCAE98@microsoft.com...
>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" <Ourania@discussions.microsoft.com> wrote in message
news:5DE844AE-07F6-41D1-B98C-E2CF94BCAE98@microsoft.com...
> 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),")",""))

"hrlngrv@aol.com" 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" <Ourania@discussions.microsoft.com> wrote in message
news:8AF6369E-F696-4947-B300-E57593BABAD7@microsoft.com...
> 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),")",""))
>
> "hrlngrv@aol.com" 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),")",""))
> >
> >