Average first 3 numbers in column e.g. 130 in 130/82
If your first number is always three digits and your second two digits,
try...
=AVERAGE(IF(A1:A100<"",LEFT(A1:A100,3)+0))
and
=AVERAGE(IF(A1:A100<"",RIGHT(A1:A100,2)+0))
Otherwise, try...
=AVERAGE(IF(A1:A100<"",MID(A1:A100,1,FIND("/",A1:A100)-1)+0))
and
=AVERAGE(IF(A1:A100<"",MID(A1:A100,FIND("/",A1:A100)+1,1024)+0))
These formulas need to be confirmed with CONTROL+SHIFT+ENTER, not just
ENTER.
Hope this helps!
In article ,
"gadmire" wrote:
Chuck:
My only problem with the below solution is that I have multiple columns next
to the column I want to average. By using the below method, I would need to
move data in many other columns to add the new column (resulting from the
split of the numbers). This would be somewhat impractical for me.
I seem to recall a formula that conditions either an average or sum that
simply takes a specified number of spaces from an entry and calculates the
result using, say, only the first three digits. If I could find this
formula, it would eliminate the need to create new columns.
Does the above ring any bells to you?
Que le vaya bien,
Gary
"CLR" wrote:
You can highlight the column and then do Data TextToColumns, and use the
/ as the delimiter.........this will separate the diastolic and systolic
numbers each into their own column.........then just average each column
using the =AVERAGE(range) formula.
Vaya con Dios,
Chuck, CABGx3
"gadmire" wrote:
I am trying to obtain an average of blood pressure over a period of time.
The individual entries are in a single column but are in a combined
format
(e.g. 130/82). I need to parse each of the two numbers and arrive at an
average of all entries in the column. I have searched help because I
thought
I had seen some reference to the selecting only a portion of a number on
a
prior search. Unfortunately, I could not come across it again. Thanks
for
any help
|