Solved

Statistic

Posted on 2014-02-19
11
140 Views
Last Modified: 2014-02-19
Hi Experts,
|
I have macro which populate low point s from the chart or from column J. I need little modification in that. I need it populated Low as well as high point & to its next column it populate its point number in Graph. like this High And Low Points With Its Point Numbers In GraphStat-2.xlsm
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
  • 7
  • 4
11 Comments
 
LVL 30

Accepted Solution

by:
gowflow earned 500 total points
ID: 39869728
Is this what you want ?

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

Set WS = ActiveSheet
WS.Range("Z:AA").EntireColumn.Delete
WS.Range("Z1") = "Extreeme Points"
WS.Range("AA1") = "Point Number"
PrevPoint = 10000000
J = 2
bDir = "Down"

For Each Ch In WS.ChartObjects
    Pts = Ch.Chart.SeriesCollection(1).Values
    MinPoint = Pts(LBound(Pts))
    MaxPoint = MinPoint
    
    For I = LBound(Pts) To UBound(Pts)
        '---> Record Lowest and Highest points
        If Pts(I) < MinPoint Then MinPoint = Pts(I)
        If Pts(I) > MaxPoint Then MaxPoint = Pts(I)
        
        If Pts(I) > PrevPoint Then
            If bDir = "Down" Then
                '---> Record ONLY the lowest point in Col Z, AA for Point number
                If Abs(PrevPoint - MinPoint) < 30 Then
                    WS.Range("Z" & J) = PrevPoint
                    WS.Range("AA" & J) = I
                    J = J + 1
                End If
                bDir = "Up"
                
            End If
        Else
            If bDir = "Up" Then
                '---> Record ONLY the Highest point in Col Z, AA for Point number
                If Abs(PrevPoint - MaxPoint) < 30 Then
                    WS.Range("Z" & J) = PrevPoint
                    WS.Range("AA" & J) = I
                    J = J + 1
                End If
                bDir = "Down"
            
            End If
            
        End If
            
        PrevPoint = Pts(I)
            
    Next I
    
Next Ch

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



End Sub

Open in new window



Check the attached file.
gowflow
Stat-2-V01.xlsm
0
 
LVL 8

Author Closing Comment

by:Naresh Patel
ID: 39869753
Yes Sir only one question. Do we accomplish this via formula. just for my knowledge. I had think on this but I dint find any solution.


Thank You
0
 
LVL 8

Author Comment

by:Naresh Patel
ID: 39869767
How do I change name of button from "Get Extreeme Points" to "Get Extreme Points". I had tried right click but I cant.


Thanks
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 30

Expert Comment

by:gowflow
ID: 39869865
No it is VBA not a formula. Do you want it as a formula ?
to change the description of the button.

1) Developper tab
2) Press on Triangle to go design mode
3) right click on the button and choose Properties
4) Change Caption to what you want
5) exit properties by clicking on the X
6) Press on design so you activate macros again


Let me know
gowflow
0
 
LVL 8

Author Comment

by:Naresh Patel
ID: 39869894
to change the description of the button.

1) Developper tab
2) Press on Triangle to go design mode
3) right click on the button and choose Properties
4) Change Caption to what you want
5) exit properties by clicking on the X
6) Press on design so you activate macros again

Done
No it is VBA not a formula. Do you want it as a formula ?

No I happy with code just need to understand logic how do I get via formula. so not exact formula for this but just logic. if it is not possible let it be.

Thanks
0
 
LVL 30

Expert Comment

by:gowflow
ID: 39869930
oooooh
You are asking for something maybe difficult. Did you see the code ? do you understand the logic?

If you want this as a UDF function that I need to work on it, I am not very strong in formulas I usually use VBA but if you want then I can look at it, for sure this you need to ask 10 questions !!! :)

just kidding, let me know if you want to post this as a question, I do not guarantee success but worse the try.

gowflow
0
 
LVL 8

Author Comment

by:Naresh Patel
ID: 39869954
No Sir No new question. actually I wondered how do I achieve this via formula. I think twice  but no success. so post a question - no matter VBA or formula. & finally found via VBA.


Now for few days say 3 to 4 days halt for new questions. working on all to gather which you had solved in recent time & past time. Merging all thoughts of back testing & predicting future.


Thanks
0
 
LVL 30

Expert Comment

by:gowflow
ID: 39870028
Good glad I could help. Will keep this one open case you need new help pls post link here.
gowflow
0
 
LVL 8

Author Comment

by:Naresh Patel
ID: 39870029
ooopppss

may I ask one new question...?

In which I have to convert shorter period data to longer period  - Say I have 1 min Data of stock price want to convert to 15 min - 30 min - 60 Min - 1 day - 1 Week - 1 month.


Thanks
0
 
LVL 8

Author Comment

by:Naresh Patel
ID: 39870100
New Question link Data convertion


Thanks
0
 
LVL 8

Author Comment

by:Naresh Patel
ID: 39872497
May I Ask New Question?
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article describes a serious pitfall that can happen when deleting shapes using VBA.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

691 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