Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 128
  • Last Modified:

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 Summary OutputBut I want only blue highlighted Cell results via excel formula.......any idea?


See attached file.
stat.xlsx
0
Naresh Patel
Asked:
Naresh Patel
  • 12
  • 8
1 Solution
 
gowflowCommented:
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
 
Naresh PatelTraderAuthor Commented:
very useful link working on it & on Scoring too.

Thanks
0
 
Naresh PatelTraderAuthor Commented:
just to recheck what am I achieving is right or wrong?
stat.xlsx
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
gowflowCommented:
0
 
Naresh PatelTraderAuthor Commented:
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.


Pls Reply


Thanks
0
 
gowflowCommented:
yes sure if we are talking macro have no problem.
gowflow
0
 
Naresh PatelTraderAuthor Commented:
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.
Regression
Thanks
Stat-2.xlsx
0
 
gowflowCommented:
Oooops this is a new issue altogether ? or I am mistaken ?
gowflow
0
 
Naresh PatelTraderAuthor Commented:
yes & I already mention in my post.....what you want me to do close this & ask very new question?


Thanks
0
 
Naresh PatelTraderAuthor Commented:
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
 
Naresh PatelTraderAuthor Commented:
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
 
gowflowCommented:
ok let me analyse this and will revert.
gowflow
0
 
gowflowCommented:
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

Open in new window


Active macros and try the attached file.
Let me know
gowflow
Stat-2.xlsm
0
 
Naresh PatelTraderAuthor Commented:
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
 
gowflowCommented:
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
 
Naresh PatelTraderAuthor Commented:
its seems working but need more data next to result column say Point Numbers. 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. High and Low togather with point number
0
 
gowflowCommented:
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
 
Naresh PatelTraderAuthor Commented:
ok got it give me 1 min


thanks
0
 
Naresh PatelTraderAuthor Commented:
Thanks
0
 
Naresh PatelTraderAuthor Commented:
here it is Statistic.


Thanks
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 12
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now