Excel Chart Graph Line/Point Issues

Microsoft Excel 2013 32/64-bit - license...
June 5, 2019 at 05:34:18
Specs: Windows 10
https://www.mediafire.com/file/2m1c...

I have an Excel where I am trying to make a graph to show data in a certain way. I am no Excel graph expert.

GOAL

What I am trying to accomplish is a chart that will show me my daily trend of OPEN, NEW, REQUESTS without numbers on the point crowding one another.

ATTEMPTS

I have tried to use formula (which I found online because I do not know much on formulas)
=IF(MOD(COUNT($B$1:B1),5)=1,B2,NA())
For the OPEN column, which does hide some numbers but the two problems I get is that the actual line is affected. For example, doing it straight up it will show my highest OPEN as 194 but using the formula to skip showing some numbers, the line never goes to 194 but hits the 170s. And of course, will not always show the current day's value.

I have tried deleting the numbers on several points manually but doing that daily and trying to figure how to add back some is tedious.

I created columns for averages for weekly and monthly (as I started this in March) but I can not seem to find a way to easily make it to show monthly numbers for March and April, weekly for May, and daily for June. My other problem is so that numbers do not clutter on the dailies, while also showing the current day’s values, I need to keep adding/removing the numbers everyday to at least bi/tri daily with current day.

Also, can not figure why I have extra hovering numbers on the left side of the graph.

Again, I am no Excel expert. If you tell me to “make a formula to calculate the averages of your biweeklies while converting them into dailies while concatenating by bilateral sum of the COUNTIF statement...” you would have already lost me at “averages.”

I have been at this for weeks making a lot of changes but always finding something to fix as the days go on. Figured time to ask for help.

Any ideas?

Thank you.

I reply as fast as I can, but it could be hours or months.


See More: Excel Chart Graph Line/Point Issues

Reply ↓  Report •

#1
June 5, 2019 at 07:04:43
Since I'm not able to see your worksheet, I'm not quite sure how you are using the MOD formula for your graph. Are you graphing only that line or are you graphing both the actual data and the results of that formula?

I don't have your data to test against, but you might be losing the max value if you are only graphing the MOD formula data. A value of 194 would result in a #N/A for that data point, which won't be graphed.

Have you tried graphing both sets of data and then setting the MOD formula line to "No Line" with Labels?

e.g. In column A I created a simple data set of the numbers 1 - 147. In B2 (my data start2 is A2)

=IF(MOD(A2,10)=0,A2,NA())

This shows a value in Column B for every 10 data points (10, 20, etc.) #N/A everywhere else.

I then graphed both lines and Formatted the MOD line as follows:

Format Data Series: Line Color = No Line
Format Data Labels: Label Options = Value

The line for the Column A data extends to the max value of 147, but the MOD line doesn't show and the labels stop at 140.

Does that help?

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#2
June 5, 2019 at 08:10:23
The worksheet is in that link.

I will see what I can do with your suggestion.

I reply as fast as I can, but it could be hours or months.


Reply ↓  Report •

#3
June 5, 2019 at 08:38:09
I cannot download files on my corporate system.

message edited by DerbyDad03


Reply ↓  Report •
Related Solutions


Ask Question