Function....

Teeroy

Well-known Member

Joined

Nov 9, 2012

Messages

2,246

Office Version

1. 365

Platform

1. Windows

• Jul 17, 2014

• #2

Median needs at least one number, although getting the Median value of a single number appears pointless to me.

In 1) the round converts the null value to 0 but 2) appears to be passing the argument as a Range object. Try:

Cells((r + rowTO), MedianUETFinterceptTo) = Application.WorksheetFunction.Median(Cells(r + rowTO, uETFinterceptTO).value)

 

Upvote 0

C

Christopher lee

Board Regular

Joined

Jul 11, 2014

Messages

79

• Jul 17, 2014

• #3

thk for fast reply teeroy. It works but if not gv me a constant value.....for median if should gv me all the same value rite? y will like this???

 

Upvote 0

C

Christopher lee

Board Regular

Joined

Jul 11, 2014

Messages

79

• Jul 17, 2014

• #4

Cells((r + rowTO), MedianUETFinterceptTo) = Application.WorksheetFunction.Median((Sheets("k1").Cells(n, 31)))

If i try this coding it gv me the "Subscript out of range" error...... my uETFintercept is on column 31....

 

Upvote 0

Teeroy

Well-known Member

Joined

Nov 9, 2012

Messages

2,246

Office Version

1. 365

Platform

1. Windows

• Jul 17, 2014

• #5

What exactly are you trying to get to? If you only pass one number to the Median function, and your Range reference using Cells only points to a single number, it will return you that number.

 

Upvote 0

C

Christopher lee

Board Regular

Joined

Jul 11, 2014

Messages

79

• Jul 17, 2014

• #6

i want to get the median of the column of uETFintercept......so the function should take all the value on column 31 and then to calculate the median of the uETFintercept and then put in the new column i create it......

 

Upvote 0

C

Christopher lee

Board Regular

Joined

Jul 11, 2014

Messages

79

• Jul 17, 2014

• #7

Cells((r + rowTO), MedianUETFinterceptTo) = Application.WorksheetFunction.Median(CStr((Round(Cells(r + rowTO, uETFinterceptTO), 6))))


Like the coding above i also can get the median of the uETFintercept with 6 decimal point....but it gv me the difference value on every row.....u know how to get the exactly median value results??

 

Upvote 0

Teeroy

Well-known Member

Joined

Nov 9, 2012

Messages

2,246

Office Version

1. 365

Platform

1. Windows

• Jul 17, 2014

• #8

Try this as an example. I think it will show you how you need to revise your code;

Code:

Sub teeroy()
Dim r As Long, uETFinterceptTO As Long, rowTo As Long
Dim Temp() As Single
uETFinterceptTO = 31 'Column required
r = 2 'Start Row
rowTo = Cells(Rows.Count, uETFinterceptTO).End(xlUp).Row 'End Row
ReDim Temp(0 To (rowTo - r))
For i = 0 To (rowTo - r)
    Temp(i) = Round(Cells(i + r, uETFinterceptTO), 6)
Next i
MsgBox Application.WorksheetFunction.Median(Temp)
End Sub

 

Upvote 0

C

Christopher lee

Board Regular

Joined

Jul 11, 2014

Messages

79

• Jul 17, 2014

• #9

wow....very thk to u 1st....i try 1st the coding can work or not....really thkful~~~~

 

Upvote 0

C

Christopher lee

Board Regular

Joined

Jul 11, 2014

Messages

79

• Jul 17, 2014

• #10

it gv subscript out of range error......how?

 

Upvote 0