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

 

[Rick Rothstein]

Perhaps you would find a UDF (user defined function) more useful...

Function Avg(CommaDelimitedString As String) As Double
  Avg = Evaluate("=AVERAGE(" & CommaDelimitedString & ")")
End Function

The above UDF will allow you to use this formula on your worksheet...

=Avg(A1)

If you are not familiar with installing UDFs, it is quite simple. Press ALT+F11 to go into the VB editor and, once there, click on Insert/Module from its menu bar, then copy/paste the code I posted above into the code window that just opened up. That's it. You can now use the function in the way I just indicated.

 

[Jonmo1]

Nice solutions...
Wish you could do that with built in functions..

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

Function Avg(CommaDelimitedString As String) As Double
  Avg = Evaluate("=AVERAGE(" & CommaDelimitedString & ")")
End Function

 

[JaredMcCullough]

Thank you very much is this UDF going to work for every instance and the one command statement worked perfectly but it was long and detailed this UDF is a lot easier but will it work for all scenarios related to this problem

 

[wigi]

but will it work for all scenarios related to this problem

That's for you to test and if not to revert. Helpers cannot know all scenarios.

 

[JaredMcCullough]

Very True but much appreciated for all the help and the promptness

 

[Jonmo1]

Thank you very much is this UDF going to work for every instance and the one command statement worked perfectly but it was long and detailed this UDF is a lot easier but will it work for all scenarios related to this problem

Why don't you try it out and see?


Yes, they should work for all occurances.
The 1 requirement is that the numbers are seperated by a comma.

It will also do any number of numbers, and any length of numbers.

 

[JaredMcCullough]

The UDF is not seeming to work with three digit numbers lets say 82,117 it works with two digit numbers like 52,54 any one have any clue what the problem is and how to fix it

 

[wigi]

This works for me, 99.5 is the result.
Please try again to see there is no typo or other mistake.

 

[Jonmo1]

I see the issue...

Wigi's works, but Ricks doesn't.

With the combination 82,117
Rick's code is seeing the Comma as a thousands seperator.
Therefore it sees it as the number 82 Thousand 1 Hundred 17


Wigi's uses the Range().Text - makes it a string instead of a number.

 

[Rick Rothstein]

The UDF is not seeming to work with three digit numbers lets say 82,117 it works with two digit numbers like 52,54 any one have any clue what the problem is and how to fix it

My guess is you are testing the UDFs against cells that are fomatted as General. Your cells must be formatted as Text, otherwise 82,117 becomes the single number 82117 and not the "number" entry you think it is. My UDFs works fine if the input is a comma separated text value.