Chart for daily events

virtuosok

Board Regular
Joined
Sep 2, 2020
Messages
72
Office Version
  1. 2016
Platform
  1. Windows
Hi,
I have a set of subjects in a database, with their symptoms logged on a given date in one of the 4 columns. Outlining this below with some simplifications.
Now the first symptom (fever) is by far the most frequent and it is logged pretty much every day - sometimes even twice or more (see last 2 rows), if there has been a manual entry error / duplicate. The rest of the symptoms are hit and miss, they are logged very irregularly, and even if they were occurring on the same day as fever, they may be logged in a wrong row (see "cramps" on 03-Jan-2020 logged in the same row as "fever" on 02-Jan-2020).
Some days are symptom-free, so no entries on that day whatsoever (e.g. for the first subject below there's a week without any symptoms between 04 Jan and 10 Jan 20).
I'm trying to see whether I can build a clustered column or maybe some other chart type, which would have on horizontal axis a custom data range (let's say 01 Jan 20 - 01 Apr 20), and then 0...4 bars on each day, depending on whether some of the symptoms were logged that day. These bars should be colored differently so that it's readily visible whether there were a lot of symptom free days for that subject in a custom data range... is it possible with the dataset in question? I don't need different height for the bars if events were logged multiple times throughout a given day - as long as a symptom has been logged at least once on a given day, I just need a bar...

Subject
Fever​
Nausea​
Cramps​
Runny nose​
00101-Jan-2020
00102-Jan-202003-Jan-2020
00103-Jan-2020
00111-Jan-202011-Jan-2020
00101-Feb-2020
00201-Mar-202001-Mar-2020
00202-Mar-202002-Mar-2020
00203-Mar-2020
00203-Mar-202005-Mar-2020
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

EXCEL MAX

Active Member
Joined
Nov 11, 2020
Messages
292
Office Version
  1. 2007
Platform
  1. Windows
Hello Virtuosok,
do you mean something like this?
Chart for daily events.PNG
 
Last edited:

virtuosok

Board Regular
Joined
Sep 2, 2020
Messages
72
Office Version
  1. 2016
Platform
  1. Windows
Hi EXCEL MAX,
Second chart would be my ideal scenario. The only problem with it for my purposes is that it either displays 4 bars or none, whereas I would love to have 1 blue bar (only) on 1st Jan in line with the entry for fever, 1 blue bar on 2nd Jan, 1 blue and 1 green bar (cramps) on 3rd Jan etc... i.e. number of bars on a given day corresponding to the # and type of symptoms that day.
It's already great that days with no symptoms whatsoever are displayed correctly - if the condition above cannot be fulfilled, can you walk me through the creation of this chart as it's already halfway through my needs?
 

EXCEL MAX

Active Member
Joined
Nov 11, 2020
Messages
292
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

This is created on Excel 2007, but let's try to go through...
Insert Clustered Column chart type.
Set your table from A1 to E10 as chart data source.
In the window for selecting cart data source click button to select new serias.
Delete series values textbox data.
Select data in the excel sheet from B1 to B10. Give name to the series.
Click OK. Click chart horizontal axis to be selected.
Now with right click on selected horizontal axis go to "Select Data". Click button "Edit".
Add data from B1 to B10 as axis label range. Click OK. Click OK again.
Resize chart horizontaly.
In the main menu go to layout and go to "Axes". Then "Primary Vertical Axes" select "None".
If we understand each other you need to got something like this...
Chart for daily events3.png

If you want columns with few Sympthoms more try to create simmilar chart and set to transparent background.
Put chart one behind each other.
 
Last edited:

virtuosok

Board Regular
Joined
Sep 2, 2020
Messages
72
Office Version
  1. 2016
Platform
  1. Windows
Thanks!
Is it possible to plot the other 3 symptoms on the same chart? Some of the days would have several bars whereas some would be "empty".
 

EXCEL MAX

Active Member
Joined
Nov 11, 2020
Messages
292
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

Here is complete solution, but you need to use VBA to hide some data points....
Insert Clustered Column chart type.
In the "Select Data Source" window set "Chart data value" to empty.
Add four series but for the all Series values use same range with dates (=YourSheetName!$B$2:$B$10").
In the "Select Data Source" window click Edit button and set same range there. Click "Ok, Ok".
Resize chart horizontaly. Hide Primary vertical axes as I explaine before,
but now you need to set in the same menu "Show Axiswith Log Scale".
Rename chart and sheet.
Now you can use this macro to make all chart data points invisible if data source for this point is empty.
This is basic idea. I beleve you are capable to adapt the rest to your needs.

Chart for daily events4.png

VBA Code:
Sub CreateDailyEventChart()
   
    Dim varWS As Worksheet
    Dim varRange1 As Range, varRange2 As Range
   
    Application.ScreenUpdating = False
    Set varWS = Sheets("YourSheetName")
    Set varRange2 = varWS.Range("B2:E10")
    varWS.ChartObjects("Chart 1").Activate
    For Each varRange1 In varRange2
        If varRange1.Value = "" Then
            ActiveChart.SeriesCollection(varRange1.Column - 1). _
            Points(varRange1.Row - 1) _
            .Format.Fill.Transparency = 1
        End If
    Next
    Application.ScreenUpdating = True
   
End Sub
 

Attachments

  • Chart for daily events4.png
    Chart for daily events4.png
    23.6 KB · Views: 3
Last edited:
Solution

EXCEL MAX

Active Member
Joined
Nov 11, 2020
Messages
292
Office Version
  1. 2007
Platform
  1. Windows
I'm glad that we find solution.
Thanks to you.
Happy New Year too.🤶
 

Watch MrExcel Video

Forum statistics

Threads
1,122,356
Messages
5,595,681
Members
414,009
Latest member
SNesbyCarr

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top