Can we find the closest date that meets a condition with Numbers?

Question

Level 1

4 points

Can we find the closest date that meets a condition with Numbers?

Hello everyone,

I created a table in which I track the number of streams a song (Dark Horse) gains on Spotify each day.

For example, Dark Horse gained 751,264 streams on August 24th (the most recent day). I would like the same cell (G2) in my Dates table to indicate that the last time Dark Horse gained less than 751,264 streams was on August 20th.

And I would like the Dates table to show, for each day, when was the last time Dark Horse gained less streams than the current day.

Thanks for your help !

Posted on Aug 27, 2024 3:58 PM

Reply

Answer 1

Badunit

Level 7

28,324 points

Aug 28, 2024 6:18 PM in response to Mana-Mahad

Formula in IG3 ="Best Day Since "& MAXIFS($1:$1,2:2,">"&IG2,$1:$1,"<"&IG$1)

Fill left to get the other columns

When you add a new column to the right it will get the formula automatically

The MAXIFS part of the formula says to get the maximum date from row 1 where the stream count is higher than the one in this column and the date is less than the date in this column. If there is no such date, the answer will be 0.

I have no data prior to 8/9/24 so my first result is "0" (highest stream count to date)

I believe your answer for column IC (8/23) in your screenshot is incorrect. It has the highest stream count of the shown data. There is no date that beats it.

I was unable to get it to format the date as MM/DD/YYYY. It wants to truncate it to MM/DD/YY

Reply

Answer 2

Badunit

Level 7

28,324 points

Aug 27, 2024 4:25 PM in response to Mana-Mahad

Here may be at least part of what you asked for, or at least the basic idea to get you started. It sounded like you wanted two indications on the same row for the same thing but I wasn't really sure. This is one of the two.

Formula in Dates::B2 =IF(MAXIFS(Data::$B$1:$G$1,Data::$B2:$G2,"<="&Data::$G2−1)=B$1,"Lower This Day","")

Fill right to complete the row (out to column F)

Fill down to complete the table

A table can have 1000 columns so your spreadsheet will have room for 999 dates.

Reply

Answer 3

Mana-Mahad Author

Level 1

4 points

Aug 28, 2024 1:00 PM in response to Badunit

Thank you again for your answer.

You were right, I now have the MAXIFS formula, I needed to update Numbers!

So, I tried this formula Dates::G2 =IFERROR(INDEX(Data::$B$1:$F$1,MATCH(Data::G2−1,Data::$B2:$F2,1)),"None smaller")

and it kinda worked, but one thing is actually bugging me.

I added a new cell with another data in it, in the "Data" tab (cell C3) and the cells E2 and H2 in the "Dates" tab now both give "August 19th" as a result.

August 19th refers to 747,000

It seems like they're only looking for the nearest value to the cell put in the MATCH formula (here 751,264 in G2) instead of the last inferior value (here August 20th with 746,995)

Is there something I can do to fix that using any of the previous formulas?

Thank you for your time.

Reply

Answer 4

Mana-Mahad Author

Level 1

4 points

Aug 28, 2024 3:39 PM in response to Badunit

Ok, so I have this table

It goes all the way back to January 1st 2024.

And I want it to look like this

Basically, I want IG3 to look back at all the previous cells in row 3 (since January 1st) to check all of them with a bigger value, and give me the latest to date (in this case ID3).

Each day, a new column will be added with a new value.

I hope it's less confusing this time.

Once again, thank you for your time, I appreciate it!

Reply

Answer 5

Mana-Mahad Author

Level 1

4 points

Aug 29, 2024 3:26 PM in response to Badunit

Hello!

You were right, I made a mistake with cell IC3, my bad.

Your formula works like a charm, thank you so much!!!

Let say I now want to find the "worst day since ..."

So, I use your formula for IG2 to check all the cells that have a lower value and find the nearest to date.

I type in IG3 ="Best Day Since "& MAXIFS($1:$1,2:2,"<"&IG2,$1:$1,"<"&IG$1)

It works, but it now looks like this

I would like the formula to give me a "0" or nothing at all when a cell has a bigger value than the previous one (row 08/27/24 and row 08/26/24 for example) instead of saying it was "the worst day since the day before".

And ideally, is there a way to combine the 2 formulas for the table to switch to "best day" when the result is 0 (or when its gives nothing) in order to look like something like this?

Once again, thank you so much for your help!

Reply

Answer 6

Mana-Mahad Author

Level 1

4 points

Aug 27, 2024 4:57 PM in response to Badunit

Thank you for your answer, but I don't think numbers has a MAXIFS formula (or I can't find it)

And sorry if I wasn't clear enough, I just want the cell in the G2 "Dates" tab to tell me when was the last time a cell was inferior to the G2 cell in the "Data" tab

Reply

Answer 7

Badunit

Level 7

28,324 points

Aug 27, 2024 6:44 PM in response to Mana-Mahad

MAXIFS is in recent versions of Numbers. I don't recall which version but it has been a little while ago.

Here is a possible solution using older functions:

Dates::G2 =IFERROR(INDEX(Data::$B$1:$F$1,MATCH(Data::G2−1,Data::$B2:$F2,1)),"None smaller")

If the IFERROR function is not in your version of Numbers:

=INDEX(Data::$B$1:$F$1,MATCH(Data::G2−1,Data::$B2:$F2,1))

If there are none that are "inferior" then it will give an error triangle.

Reply

Answer 8

Badunit

Level 7

28,324 points

Aug 28, 2024 2:26 PM in response to Mana-Mahad

The first formula I gave was designed for the table you showed in your first post. You changed the table but did not adjust the formula for your changes. Not that it really matters, though, because you said it didn't do what you wanted.

I thought the second formula I posted was what you wanted. Maybe it is/was but you've changed your table so it, too, will no longer work correctly.

Tell more about how this table is going to change over time. If you are going to be adding or removing columns, you will need formulas that will adjust on their own or a procedure to follow so the formulas don't break.

Tell/show again what you want as a result. Make your Dates table look like what you want it to, but not using formulas. Type in the results you expect to see in each cell. Provide a screenshot of the two tables.

Reply

Answer 9

Mana-Mahad Author

Level 1

4 points

Aug 29, 2024 4:02 PM in response to Mana-Mahad

Nevermind, I think I've found the solution!

I simply used the IF formula

One last time, thank you so much, you helped me a lot!

Reply