Histogram - show the last 30 entries only

**mrdaave** · 08-21-2023, 04:23 AM

Dear members of the forum,

I have made a table, which updates itself every 24 hours and feeds the data into a histogram.
After some time the histogram is now overloaded with data and you can't read the information anymore.

Is there a possibility to show just the last 30 entries of the table in the histogram?
For example: if the table updates itself today (21.08.2023), the information of the 22.08.2023 should be not visible anymore/deleted.
For better understanding I attached a sample workbook.

Thank you in advance and best regards,

David

**AliGW** · 08-21-2023, 04:35 AM

If today is 21 August, why would you want to hide tomorrow?

Do you mean from today onwards?

**HansDouwe** · 08-21-2023, 04:42 AM

Select for your histagram the result of this formula:

Formula:

Please Login or Register  to view this content.

**mrdaave** · 08-21-2023, 04:42 AM

Hi Ali,
sorry - typing mistake...

Should be 22.07.2023

**AliGW** · 08-21-2023, 04:44 AM

That makes a BIG difference!

However, the data you have provided starts from today and does not go back that far.

**HansDouwe** · 08-21-2023, 04:48 AM

Did you see post #3?
It shows the last 30 entries only.

**mrdaave** · 08-21-2023, 04:52 AM

I know - I can change the sample workbook. But 22.07.2023 - 21.08.2023 should just be an example. The important part is, that I would just like to see the last 30 entries

**AliGW** · 08-21-2023, 04:53 AM

I'm confused about the requirements. The data is from today only, but the OP wants to go back one month and cut off anything prior to that, as far as I understand it.

I think the solution will include FILTER and EDATE, but I don't believe the sample data reflects the request.

**mrdaave** · 08-21-2023, 04:53 AM

@HansDouwe,
thank you for your help! I've tried to implement this in my real worksheet, but I just get #VALUE...

**AliGW** · 08-21-2023, 04:54 AM

The important part is, that I would just like to see the last 30 entries

Go with Hans' suggestion, then. If the date is not important, you didn't need to mention it at all.

**mrdaave** · 08-21-2023, 04:54 AM

@AliGW - give me a minute - I will change the samplesheet

**AliGW** · 08-21-2023, 04:55 AM

No need - Hans has it sorted.

**mrdaave** · 08-21-2023, 04:57 AM

Here we go

**HansDouwe** · 08-21-2023, 04:59 AM

but I just get #VALUE...

Please check the references of the formula or upload the workbook (without confidental data) that produces the error.

**AliGW** · 08-21-2023, 05:00 AM

Use this and base the histogram on this filtered data:

=FILTER(A3:B600,(A3:A600<>"")*(A3:A600>EDATE(TODAY(),-1)+1))

=FILTRE(A3:B600;(A3:A600<>"")*(A3:A600>MOIS.DECALER(AUJOURDHUI();-1)+1))

**mrdaave** · 08-21-2023, 05:01 AM

The only difference is that in the final workbook the data is in a table. Can I still "TAKE and FILTER" there?

**AliGW** · 08-21-2023, 05:02 AM

Or:

=FILTER(A3:B600,(A3:A600<>"")*(A3:A600>TODAY()-30))

=FILTRE(A3:B600;(A3:A600<>"")*(A3:A600>AUJOURDHUI()-30))

The only difference is that in the final workbook the data is in a table. Can I still "TAKE and FILTER" there?

The source data can be in a table, but not the results data. But that shouldn't matter - share a more realistic workbook!

**HansDouwe** · 08-21-2023, 05:08 AM

Did you see post #14?

**mrdaave** · 08-21-2023, 05:12 AM

@Hans - I could solve the problem using ";" instead of "," Thank you!

@Ali - the formula works, but when the data updates itself, the filtered data gets deleted.

**HansDouwe** · 08-21-2023, 05:20 AM

Thanks for your feedback.
Does the formula do what you want?

If so, if you haven't already, please consider adding reputation to all helpers you think they deserve.
In that case please click * Add Reputation left below their answers.

**AliGW** · 08-21-2023, 05:28 AM

@Ali - the formula works, but when the data updates itself, the filtered data gets deleted.

It can’t do if you’ve used it correctly!!!

**mrdaave** · 08-21-2023, 05:34 AM

If the data updates itself it adds one row in the table, copy pastes the data and sort the table from oldest to newest (actual date is on the bottom of the table).
I will try to play with it an may find the problem myself (Y)

**AliGW** · 08-21-2023, 05:35 AM

Again, it should work - share a workbook. I don’t have a crystal ball.

**mrdaave** · 08-21-2023, 05:52 AM

Found the problem - somehow the macro for updating the data changes the formula in a strange way...
I've added your formula to a different sheet and now everything works fine!

I would have shared another workbook, but the real data is complicated - i would take me days to make a useful sample sheet. :/

Thank you for your patience and amazing help - you saved me many times already!

**AliGW** · 08-21-2023, 06:07 AM

OK - as long as you have it working.

Histogram - show the last 30 entries only

LinkBack

Thread Tools

Rate This Thread

Display

Histogram - show the last 30 entries only

Re: Histogram - show the last 30 entries only

Re: Histogram - show the last 30 entries only

Re: Histogram - show the last 30 entries only

Re: Histogram - show the last 30 entries only

Re: Histogram - show the last 30 entries only

Re: Histogram - show the last 30 entries only

Re: Histogram - show the last 30 entries only

Re: Histogram - show the last 30 entries only

Re: Histogram - show the last 30 entries only

Re: Histogram - show the last 30 entries only

Re: Histogram - show the last 30 entries only

Re: Histogram - show the last 30 entries only

Re: Histogram - show the last 30 entries only

Re: Histogram - show the last 30 entries only

Re: Histogram - show the last 30 entries only

Re: Histogram - show the last 30 entries only

Re: Histogram - show the last 30 entries only

Re: Histogram - show the last 30 entries only

Re: Histogram - show the last 30 entries only

Re: Histogram - show the last 30 entries only

Re: Histogram - show the last 30 entries only

Re: Histogram - show the last 30 entries only

Re: Histogram - show the last 30 entries only

Re: Histogram - show the last 30 entries only

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Trying to make a histogram following https://trumpexcel.com/histogram-in-excel - help

[SOLVED] Histogram with Benchmark, adding a point to show where Curve meets benchmark.

Histogram x-axis need to show more values for x-axis to be able to read mean,median,mode

Re: histogram x-axis data (BINs) are not accounting for all the data entries

[SOLVED] Pivottable won't show new entries

[SOLVED] I was creating a histogram; now i have data but no histogram.

[SOLVED] How to Histogram w/o raw data but histogram Table on Excel

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions