SOLVED

Formula help please

Brass Contributor

I have a sheet with a year of (365) dates in Row 1 starting in cell B1

Under that is a list types of incidents in column A

The requirement is to look through each day per incident type to find the Max number of incidents and the date of that 'peak'. I can find the maximum number in the row with a formula. But what do I need to add to give me the (Vector?) of the corresponding date that will be above in Row 1?

15 Replies

@Clive_Rabson 

This will get the first match.

=XLOOKUP(Maximum,B2:NC2,$B$1:$NC$1)

 

Detlef Thank you.
But that does not work for me. Could it be because I am using Excel Mac 16.78 on Mac OS Monterey 12.7.4?
Using your suggestion I get: #name?

@Clive_Rabson 

You should update. The latest version is 16.84 from April 2024.

 

https://learn.microsoft.com/en-us/officeupdates/update-history-office-for-mac

 

@Detlef Lewin 

 

Thanks.  Yes, I know, but I don’t think I can update any further on my 2013 MacBook Pro

@Clive_Rabson 

I understand.

The use INDEX/MATCH.

=INDEX($B$1:$NC$1,MATCH(Maximum,B2:NC2,0))
Detlef
Thanks but again that gives the result: #NAME?

@Clive_Rabson 

I should be clear that Maximum must be replaced with the cell reference where you calculate the max.

Detlef
That is successful.
Grateful thanks to you.

Please could we take this one step further?

In the same sheet I also need to find the cell with first occurrence of an incident in the year (the row). Also, I need the the last occurrence of an incident in the same row.

Thanks

@Clive_Rabson 

best response confirmed by Clive_Rabson (Brass Contributor)
Solution

@Clive_Rabson 

 

=TAKE(FILTER($B$1:$NC$1,B2:NC2<>""),,1)
=TAKE(FILTER($B$1:$NC$1,B2:NC2<>""),,-1)

=INDEX($B$1:$NC$1,AGGREGATE(15,6,COLUMN($B$1:$NC$1)/(B2:NC2<>""),1)-1)
=INDEX($B$1:$NC$1,AGGREGATE(14,6,COLUMN($B$1:$NC$1)/(B2:NC2<>""),1)-1)
Detlef, thank you.
Are those four formulae alternatives?
If so what are they dependent on?

@Clive_Rabson 

The first two need a newer version of Excel. The other two need at least Excel 2010.

 

@Detlef Lewin Hi again. Sorry to trouble you with a banal question. This week I cannot see a way of starting a new post. Can you assist please?

I have another formula question

@Detlef Lewin 

Hello, I have another question.

I have a simple spreadsheet with three columns

Date1   Date2   ChosenDate

 

The cell ChosenDate needs to show the earliest date of Date1 and Date2

I thought this would work =IF(Date1<Date2,Date1,IF(Date1=Date2,Date1,IF(AND(Date1="",Date2="","none"))))

But it doesn't work. Any clue?

1 best response

Accepted Solutions
best response confirmed by Clive_Rabson (Brass Contributor)
Solution

@Clive_Rabson 

 

=TAKE(FILTER($B$1:$NC$1,B2:NC2<>""),,1)
=TAKE(FILTER($B$1:$NC$1,B2:NC2<>""),,-1)

=INDEX($B$1:$NC$1,AGGREGATE(15,6,COLUMN($B$1:$NC$1)/(B2:NC2<>""),1)-1)
=INDEX($B$1:$NC$1,AGGREGATE(14,6,COLUMN($B$1:$NC$1)/(B2:NC2<>""),1)-1)

View solution in original post