Excel Array Formula Assistance

Question

I'm after a bit of Excel help and was hoping one of you might be able to oblige. I have a sheet of data (exported from Apple Health) that essentially contains columns for date, hour, number of steps. Sample data...

Sample Data

I've managed to create an array formula that calculates the max number of steps in a single day, using the following...

{=MAX(SUMIF(Table1[DATE],Table1[DATE],Table1[STEPS]))}

I now want to create another formula that tells me which date that was.

I'm familiar with Index and Match, but not having much luck.

Any help and assistance appreciated.

Thanks :)

make a pivot table to summarize your table 1 and set it up so you can read what you ask for from there — Mats Lind
– Mats Lind, Commented Sep 9, 2016 at 9:31
I was hoping to do it without having to create a pivot or any additional columns. Personal challenge as much as anything to further my knowledge in Excel. :) — user2976984
– user2976984, Commented Sep 9, 2016 at 9:34
I see, and I guess you will learn that the hard way in due time as you will be given old near-impossible-to-read excel workbooks were the user have build it all from scratch with his or her own excel formula. Take the opportunity to learn how to buid stuff with standardized Tools that are fast, and easy to understand and maintain! — Mats Lind
– Mats Lind, Commented Sep 9, 2016 at 9:42
Won't that formula return the total number of steps for the day rather than the maximum? i.e. using your sample data it returns 4711 (total of visible data) rather than 3383 (maximum value in 04/07/2016). You could remove the MAX and array formula part and still get the same result. — Darren Bartrup-Cook
– Darren Bartrup-Cook, Commented Sep 9, 2016 at 12:32

Darren Bartrup-Cook · Accepted Answer · 2016-09-09 12:41:13Z

1

To add to my comment in the OP:

This formula will show the maximum number of steps for the required date, where the required date is entered in cell I6.
{=MAX(IF(Table1[Date]=$I$6,Table1[Steps]))}

This formula will return the date:
{=INDEX(Table1[Date],MATCH(MAX(IF(Table1[Date]=$I$6,Table1[Steps])),Table1[Steps],0))}

answered Sep 9, 2016 at 12:41

Darren Bartrup-Cook

20.2k2 gold badges27 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user2976984 Over a year ago

Many thanks... my original formula returned the number of steps for the day which had the greatest sum. I've also sussed out the second bit that I was after too - finding which day that was. The answer was staring me in the face in the end. Ta :)

Collectives™ on Stack Overflow

Excel Array Formula Assistance

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related