Solved

Statistic

Posted on 2014-02-14
20
125 Views
Last Modified: 2014-02-19
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
Comment
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
  • Learn & ask questions
  • 12
  • 8
20 Comments
 
LVL 30

Expert Comment

by:gowflow
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

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

Thanks
0
 
LVL 8

Author Comment

by:Naresh Patel
ID: 39858727
just to recheck what am I achieving is right or wrong?
stat.xlsx
0
Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 
LVL 30

Expert Comment

by:gowflow
ID: 39858991
0
 
LVL 8

Author Comment

by:Naresh Patel
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.


Pls Reply


Thanks
0
 
LVL 30

Expert Comment

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

Author Comment

by:Naresh Patel
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.
Regression
Thanks
Stat-2.xlsx
0
 
LVL 30

Expert Comment

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

Author Comment

by:Naresh Patel
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

by:Naresh Patel
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

by:Naresh Patel
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 30

Expert Comment

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

Accepted Solution

by:
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

Open in new window


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

Author Comment

by:Naresh Patel
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 30

Expert Comment

by:gowflow
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

by:Naresh Patel
ID: 39869681
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
 
LVL 30

Expert Comment

by:gowflow
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

by:Naresh Patel
ID: 39869691
ok got it give me 1 min


thanks
0
 
LVL 8

Author Closing Comment

by:Naresh Patel
ID: 39869692
Thanks
0
 
LVL 8

Author Comment

by:Naresh Patel
ID: 39869702
here it is Statistic.


Thanks
0

Featured Post

SendBlaster Pro 4 - Bulk Email Sending Software

SendBlaster 4 Pro - Best Bulk Emailing Sending Software
Automatic Subscribe / Unsubscribe Processing
Great for Newsletters & Mass Mailings
Optional HTML & Text Composition
Integration with Google Features
Built in Spam Score Checking
Free Professional Templates - Feature Packed!

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question