Easy question??

[JaredMcCullough]

I just have a general problem i am trying to simplify my spreadsheet by making corresponding rectangles lets say cell A1 contains two values one a high and one a low (11,12) is it possible for me to average these two numbers and have that average in C1. To continue then B1 would be (15,16). And i would want D1 to be the average of these numbers.
A2 would be (11,22) and I want C2 to be the average of these two. It may be something simple but i have become completely dumbfounded by it thanks

 

[wigi]

The 2 numbers are in the same cell?
Why don't you use 2 columns, then the AVERAGE function will be very easy.

 

[Jonmo1]

Welcome to the board..

I agree with wigi, it would be sooooo much simpler if you just put the numbers in seperate cells like A1 and B1.
Then just use =AVARAGE(A1,B1)


But in the interest of "What the heck!!"

Try
=AVERAGE(LEFT(A1,FIND(",",A1)-1)+0,REPLACE(A1,1,FIND(",",A1),"")+0)

This will work for your specific example, if A1 contains only 2 numbers, seperated by a comma.
Any more than that, it's going to get difficult.


Hope that helps.

 

[JaredMcCullough]

Because essentially it is an eight by eight rectangle meaning if i created a high low column with A being high B being low and C being average then there would be 64 items just for one individual rectangle and there are mutiple that are gonna be in the spread sheet so what i was going to do was put the high low values in one cell that way i could create the rectangle in an 8 by 8 rectangle format and beside it i would make another 8 by 8 rectangle format that contained the averages such that the two values in J1 (Skipping I to denote seperate rectangles) would be equal to the average of the two values in A1

 

[T. Valko]

I just have a general problem i am trying to simplify my spreadsheet by making corresponding rectangles lets say cell A1 contains two values one a high and one a low (11,12) is it possible for me to average these two numbers and have that average in C1. To continue then B1 would be (15,16). And i would want D1 to be the average of these numbers.
A2 would be (11,22) and I want C2 to be the average of these two. It may be something simple but i have become completely dumbfounded by it thanks

Are the numbers always 2 digits?

 

[Rick Rothstein]

I assumed those parentheses you showed are not really in the cell with your numbers. Put this formula in C1...

=(LEFT(A1,FIND(",",A1)-1)+MID(A1,FIND(",",A1)+1,9))/2

Copy it left to D1, then select C1:D1 and copy it down as far as needed.

 

[JaredMcCullough]

they range from single to three digit numbers and no the parenthesis are not they were just to show the numbers within the cell

 

[Jonmo1]

My formula in post#3 will accomodate numbers of any length.
But only 2 numbers in the cell, seperated by a comma.

 

[T. Valko]

they range from single to three digit numbers and no the parenthesis are not they were just to show the numbers within the cell

Ok, I assume these cells are formatted as TEXT.

Try this...

=AVERAGE(--LEFT(A1,FIND(",",A1)-1),--MID(A1,FIND(",",A1)+1,3))

 

[wigi]

Hello,

given the layout of the data, you can use this UDF:

Function GetAverage(rng As Range) As Double
    
    GetAverage = Evaluate("=AVERAGE(" & rng.Text & ")")

End Function

Please insert the code in a normal module in VBA.

In a sheet, you can then use for instance:

=GetAverage(A1)

if you want to apply the average of the numbers in cell A1.

The advantage is that you can have many numbers, all in 1 cell !