0

I have this data (as you can see I did zero pushups on the 21st/23rd/24th):

Date       Pushups
9/20/2020  150
9/22/2020  100
9/25/2020  200

What is the easiest way to make a histogram with this data?
It should show empty days too, and have histogram bars, not points floating in the air. It should look like this:

^
|         β–ˆ
| β–ˆ       β–ˆ                             πŸ‘
| β–ˆ  β–ˆ    β–ˆ
| β–ˆ  β–ˆ    β–ˆ
+----------->

In particular, it should not look like this:

^
|     β–ˆ
| β–ˆ   β–ˆ                                 πŸ‘Ž
| β–ˆ β–ˆ β–ˆ
| β–ˆ β–ˆ β–ˆ
+-------->

I have thousands of rows, so inserting empty rows is not a good solution (unless there is a quick way to do it in a annex sheet).

A scatter chart is not a good option either, unless anyone knows a trick to make it show bars instead of points in the air:

enter image description here πŸ‘Ž

1

Chart only uses data you input, it won't guess and fill the blanks.

Thus as a solution recommend using this supporting formula and build chart based on its results:

={SEQUENCE(MAX(A:A)-MIN(A:A) + 1,1,MIN(A:A),1),
  ARRAYFORMULA(IFERROR(VLOOKUP(SEQUENCE(MAX(A:A)-MIN(A:A) + 1,1,MIN(A:A),1),$A:$B,2,0),0))}

It creates sequence of dates starting from the oldest to the last one in your original data and then VLOOKUP values. If the date is missing in your dataset then it puts 0.

Range A:B is your original data range.

Next step: Format the left column as date, and select the generated table create the histogram as seen below.

enter image description here

| improve this answer | |
  • Wonderful, that works great! – nic Sep 25 at 13:07

Your Answer

By clicking β€œPost Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.