Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
=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. |
#5
|
|||
|
|||
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),")","")) |
#6
|
|||
|
|||
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. |
#7
|
|||
|
|||
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. |
#8
|
|||
|
|||
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),")","")) |
#9
|
|||
|
|||
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),")","")) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
CAN I ADD NUMBERS IN ONE CELL | Excel Worksheet Functions | |||
Paste rows of numbers from Word into single Excel cell | Excel Discussion (Misc queries) | |||
GET.CELL | Excel Worksheet Functions | |||
combining several individual cells of numbers into one cell | Excel Worksheet Functions | |||
How do you extract numbers from a string of chacters in a cell (E. | Excel Worksheet Functions |