Share via

How to create chart with "y" axis a count of number of entries

Anonymous
2015-04-12T20:03:47+00:00

I know just enough to be dangerous ... please be gentle.

I have a spreadsheet with data gathered over time.

Entries are made by date.  A given date may have up to four entries.  Thanks to shoddy bookkeeping no data were collected on some days.  Those days are simply left out; dates [Column A] can "skip", for example, from Mar 17 on row 128 to Mar 19 on row 129.

I want to create a scatter chart where the "X" axis is the range of data [column C], and the "Y" axis is the number of days over which the data was collected. At least I think I do, knowing what data to put in "X" and "Y" always confuses me.  I'm a much better consumer of number "stuff".  I'm not to shoddy about gathering it. I am terrified of manipulating and distributing it.  Thus my cry for help here. 

[Readings]

100

90

80

70

60

50

40

30

20

10

0    5   10   15   20   25 >>>>>>>>>>>>>>>>>>>>> [Days]

I've go the "X" figured out; well, Excel does.  I just selected a range of entries in Column C and the chart wizard took care of it.

I cannot figure out how to count the number of entries by occurrence and display them in my chart, sort of like the mock-up above. The potential is for 124 entries.  I'm just guessing that if displayed in increments of 5, it will fit on one page if I use landscape.

This sentence will be meaningful to some: "I am an English teacher and am breaking out in hives just typing this." 

tnx

mcm

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

  1. Anonymous
    2015-04-14T00:53:02+00:00

    Not really. If you don't care about the date at all then you could produce something like your first graph, but that won't show the data 'gaps' where days are missing. Alternatively you can format the axis with a minimum amount of information, like this. Obviously you can alter the default spacing etc as required.

    To achieve something like this - assuming your data is in columns A,B,C as per picture

    Put headings DATE and READINGS in columns E and F

    Enter your first date in E2 and use the fill handle to fill down until you reach the end date

    Enter the following formula in F2, changing the '10' to be the bottom row of your data.

    =SUMIF($A$2:$A$10,"="&E2,$C$2:$C$10)

    Copy that formula all the way down to the last date

    Select all cells in range E1:F?

    Insert a 'Scatter' chart.

    PS: Don't worry about the paper size too much - Excel will compress the graph to fit all the data and it will print on whatever paper size you choose.

    Regards

    Murray

    1 person found this answer helpful.
    0 comments

4 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2015-04-13T05:22:13+00:00

    Is this something more like what you want Mark? Dates along the X axis, count of total readings by day on the Y axis. So in this case from your data 14-Mar is 241=110+131?

    If so please advise and I will tell you how to achieve.

    To explain the result you got by selecting and graphing a single column - Excel treats each value as a 'Y', and assigns each a sequential X value. Thus point X=1 on your graph Y=110, point X=2, Y=131, point X=3, Y=313 and so on.

    By the way, the 'independent' variable (the one that you can't control; time in your case) is generally plotted on the X (horizontal) axis. The 'dependent' variable (the one that changes in response to the independent variable; readings in your case) is generally plotted on Y.

    1 person found this answer helpful.
    0 comments
  2. Anonymous
    2015-04-13T11:39:30+00:00

    That might be a better approach. I am still dealing with the limitation of fitting on 8X10 paper.  I don't need the "month" on every entry though.  Will Excel let me make two-ling entries on the horizontal axis?  so I could do something like:

    13  14  15  16  17  18 >>>31  1  2  3  4 >>>>

    Mar                                  Mar  Apr

    mark

    0 comments
  3. Anonymous
    2015-04-13T04:50:26+00:00

    It would be helpful if you could provide an idea of your data and layout, either as a snapshot or a mock-up table. Otherwise it's not clear to me.

    Also, for example and given your X-Y confusion (!) you say you want range of data on X and number of days on Y, yet your pseudo figure above shows days on the X axis.

    Regards

    Murray

    Told you X and Y confused me!! I don't really care which is which -- I defer to anyone who "does" math regarding what best goes where.

    Here's some of my data as I entered into Excel 2013:

    And this is what I was able to get with the chart wizard:

    The vertical access looks like I envision it.  It's the horizontal one that I don't get.  The more I look at it, the more confused I get.  Since all the data is going to be clustered closely together, displaying the number of days as I initially thought isn't really going right, is it.  This is why I taught English.

    tnx for taking a look.

    mark

    0 comments
  4. Anonymous
    2015-04-13T04:29:07+00:00

    It would be helpful if you could provide an idea of your data and layout, either as a snapshot or a mock-up table. Otherwise it's not clear to me.

    Also, for example and given your X-Y confusion (!) you say you want range of data on X and number of days on Y, yet your pseudo figure above shows days on the X axis.

    Regards

    Murray

    0 comments