Statistic

Hi Experts,

I have prices in column  A & variables in column C D E F. I want formula for t StatCoefficients.
I had done via Tab - Data - Data Analysis - Regression - Results in same sheet which  is look like this But I want only blue highlighted Cell results via excel formula.......any idea?

See attached file.
stat.xlsx
LVL 8
Who is Participating?

x

Commented:
ok here it is. I created a button Get Lowest Points with the below sub

``````Sub GetLowestPoints()
Dim WS As Worksheet
Dim Ch As ChartObject
Dim Pts As Variant
Dim I As Long, J As Long
Dim PrevPoint As Double
Dim MaxRow As Long
Dim bDir As String

Set WS = ActiveSheet
WS.Range("Z:Z").EntireColumn.Delete
WS.Range("Z1") = "Lowest Points"
PrevPoint = 10000000
J = 2
bDir = "Down"

For Each Ch In WS.ChartObjects
Pts = Ch.Chart.SeriesCollection(1).Values

For I = LBound(Pts) To UBound(Pts)

If Pts(I) > PrevPoint Then
If bDir = "Down" Then
'---> Record ONLY the lowest point in Col Z
If J = 2 Then
WS.Range("Z" & J) = PrevPoint
J = J + 1
End If

If J > 2 And Abs(PrevPoint - WS.Range("Z" & J - 1)) < 30 Then
WS.Range("Z" & J) = PrevPoint
J = J + 1
End If
bDir = "Up"

End If
Else
If bDir = "Up" Then
bDir = "Down"
End If

End If

PrevPoint = Pts(I)

Next I

Next Ch

WS.Range("Z:Z").EntireColumn.AutoFit

End Sub
``````

Active macros and try the attached file.
Let me know
gowflow
Stat-2.xlsm
0

Commented:
Not too familiar with ANOVA but found this if you know what you want maybe you can find your way there.

http://formulas.tutorvista.com/math/anova-formula.html

gowflow
0

very useful link working on it & on Scoring too.

Thanks
0

just to recheck what am I achieving is right or wrong?
stat.xlsx
0

Commented:
0

Sir gowflow,

May I reframe the new question as I don't found any Formula. I found macro recording is working. so I thought just go ahead with this same question link with same question next step.

Thanks
0

Commented:
yes sure if we are talking macro have no problem.
gowflow
0

Not too much in next step. need to find numbers of days between regression cycle. I had calculated  regression in column J. I have one excel chart in attached WB in that need to find numbers of the days between A low to B low.

Thanks
Stat-2.xlsx
0

Commented:
Oooops this is a new issue altogether ? or I am mistaken ?
gowflow
0

yes & I already mention in my post.....what you want me to do close this & ask very new question?

Thanks
0

Stat 1 & Stat 2 are actually same -  In stat1 I had copy past special Close & ABCD which is in stat 2 "2pi*/365, cos€, sin€,      cos(2e), sin(2e)". stat 1 I thought not required confusing formula for A BCD so I just pasted values.

Thanks
0

For this question - only need to know how do I get numbers of data point or say Trading Days between Point A low To Point Low B in Chart.

Thanks
0

Commented:
ok let me analyse this and will revert.
gowflow
0

Perfect Macro it will useful  surely in future....& I am not just saying,  i will... but not for this process...as in this process I need numbers of data point (rows) between 2 lows (& only 2 lows - any two lows) as this is regression so lows & high are identical. actually I am finding duration between two lows.

if you wish to understand than read this as this technical part of this.
Purpose of doing this - by finding duration of cycle we can pin point low or high of stock price. we just need to find where we are in cycle & we can predict future high & low. this is very early stag of this there 3 or 4 more cycles.

apology that I cant make you understand perfectly what I want & you invested your time in this Code but I gona use this Code surly.

Thanks
0

Commented:
Actually if you notice there are 2 lows I picked the lowest as you had indicated in red. but as I read the post it says that it is interesting to capture low and high of each cycle.

anyway anything wrong in the macro please let me know.
gowflow
0

its seems working but need more data next to result column say . results may be some thing else then shown as I gave match formula & it shows this.

This is addition to actual question so if you want me ask different question then I will else
Add High points too with its point numbers.
i.e.
0

Commented:
Well as this question is already TOTALLY out of focus from the initial post, I prefer to ask a new related question.

Please do not forget to post a link of the new question if you need my help.
gowflow
0

ok got it give me 1 min

thanks
0

Thanks
0

here it is Statistic.

Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.