Solved

# Statistic

Posted on 2014-02-14
126 Views
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
0
Question by:Naresh Patel
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 12
• 8

LVL 31

Expert Comment

ID: 39858440
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

LVL 8

Author Comment

ID: 39858489
very useful link working on it & on Scoring too.

Thanks
0

LVL 8

Author Comment

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

LVL 31

Expert Comment

ID: 39858991
0

LVL 8

Author Comment

ID: 39866959
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

LVL 31

Expert Comment

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

LVL 8

Author Comment

ID: 39866990
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

LVL 31

Expert Comment

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

LVL 8

Author Comment

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

Thanks
0

LVL 8

Author Comment

ID: 39867009
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

LVL 8

Author Comment

ID: 39867053
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

LVL 31

Expert Comment

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

LVL 31

Accepted Solution

gowflow earned 500 total points
ID: 39867364
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

LVL 8

Author Comment

ID: 39867851
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

LVL 31

Expert Comment

ID: 39867978
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

LVL 8

Author Comment

ID: 39869681
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

LVL 31

Expert Comment

ID: 39869686
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

LVL 8

Author Comment

ID: 39869691
ok got it give me 1 min

thanks
0

LVL 8

Author Closing Comment

ID: 39869692
Thanks
0

LVL 8

Author Comment

ID: 39869702
here it is Statistic.

Thanks
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are â€¦
My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaacâ€¦
###### Suggested Courses
Course of the Month8 days, 23 hours left to enroll