0

I am trying to create a dashboard graph in Excel from a single column with dates, N/A and blanks, like this:

  • Date Submitted to Planning
  • 20/11/2015
  • 20/11/2015
  • 13/11/2015
  • {blank}
  • N/A
  • {blank}
  • {blank}
  • 17/11/2015
  • 19/11/2015
  • 22/11/2015

What I want to show is number submitted i.e. out of the 9 rows, 6 have been submitted as a doughnut graph (or similar).

I can count the rows for the total number but I'm struggling with how to count the dates and display the results in a format that doesn't just show 1 to 9.

1
  • 1
    Are the dates stored as Excel dates or text? Is N/A text or the Not Available function (#N/A, =NA())?
    – fixer1234
    Feb 22, 2016 at 8:11

1 Answer 1

1

I'm going to make a few assumptions:

  • The dates are stored as dates and the day/month/year sequence is locale-based formatting.
  • The N/A is actually the NA() function, and the values are displayed as #N/A. (Actually, with the solution below, N/A can be just text.)
  • That what you're looking for is something like this:

donut chart

The count of dates is done with:

=COUNT(A2:A11)

The COUNT function counts only number entries, and dates are stored as numbers.

The count of blanks is done with:

=COUNTBLANK(A2:A11)

I did this in LibreOffice Calc, so the menu selections are a little different, but the basic steps should be:

  • Select the two counts
  • Insert Chart
  • Select Donut chart
  • Pick the options you want for the display (legend, values and/or percentages, etc.)

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .