• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Vertical line pointing to fixed Date

H.H

New Member
Hi Friends,

This is my first question :DD
In majority of my pivot charts, I need to draw a vertical line to point to specific Date
like below example .JPG



Normally, I draw it using Insert/ Shapes
but it has one drawback, each time to Update my chart with new Daily data,
it moves and I've to drag it back to the specific Date point (example: 24th. February 0:00)

Is there any solution to force the Verticall line to be always pointing to specific date (example: 24th. February 0:00)
and never move regardless of the new daily data I will keep updating !
 

Attachments

Mike86

Active Member
The easiest way is to put a second plot on the chart with two points, 0 and 100, at whatever date-time you desire. Fix the second Y-axis to 0 and 100. Turn on the line between the points and turn off the markers. Hide the second axis.
 

H.H

New Member
The easiest way is to put a second plot on the chart with two points, 0 and 100, at whatever date-time you desire. Fix the second Y-axis to 0 and 100. Turn on the line between the points and turn off the markers. Hide the second axis.
Hi Mike,
I know, it is strange to reply after several years :)
I still didn't solve this issue,
could you mind share draft excel file example of your proposed solution.
Thank you
 

vletm

Excel Ninja
H.H
HInt:
It's You, who should able to upload a sample data.
- You seems to have .. had that data few years ago.
as well as expected output
- even done manually based Your given sample data.
Or
do You expect to get JPG-file based some random data?
 

H.H

New Member
you are right,
here is one example chart, where I want to configure vertical line point at 7 PM for 1st. September
objective is to have this vertical line always at this time interval even after I will update my average value time line with new updated inputs
 

Attachments

H.H

New Member
Thanks a lot vletm for sharing the idea,
just please want to know how did you add spot to Pivot chart table
as normally when I add it by myself it come with average of spot (check below screenshot compare between yours & mine)
 

Attachments

H.H

New Member
Yes I saw it and got the formula and the idea
-My inquiry is regarding inserting Spot into table chart (looks like you are adding it as Error Bar, etc) - can you share steps?
just kindly have a look on below image
 

Attachments

vletm

Excel Ninja
Those Your snapshots won't help anything ...
As well as You could try to figure something based below snapshot ...
Screenshot 2021-09-18 at 10.47.40.png
 

vletm

Excel Ninja
Why You take average of spots from Pivot?
You can use those spot-values ... those gives already You needed results.
...and this way, there won't be those You #DIV/0! 's.
My sample was a quick test days ago
and then Error-bar looks suitable with that.
 

H.H

New Member
Why You take average of spots from Pivot?
You can use those spot-values ... those gives already You needed results.
...and this way, there won't be those You #DIV/0! 's.
Totally agree but the only way I know is doing by Average/ Sum :rolleyes:
that is why I posted asking kindly to share how you did it
so I can use this way for other excel files later
 

vletm

Excel Ninja
Please, reread my second line ...
... and answer for Yourself - why need to take average/sum if You'll have only one value?
I won't use sheets, which has visible errors - if possible.
... can I (You) use ... ? That depends of You.
 
Top