Forum Discussion

Clive_Rabson's avatar
Clive_Rabson
Brass Contributor
May 09, 2024
Solved

Formula help please

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 ...
excel
  • Detlef_Lewin's avatar
    Detlef_Lewin
    May 12, 2024

    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)
Clive_Rabson's avatar
Clive_Rabson
Brass Contributor
May 10, 2024
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's avatar
    Clive_Rabson
    Brass Contributor
    May 10, 2024

    Detlef_Lewin 

     

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

    • Detlef_Lewin's avatar
      Detlef_Lewin
      Silver Contributor
      May 10, 2024

      Clive_Rabson 

      I understand.

      The use INDEX/MATCH.

      =INDEX($B$1:$NC$1,MATCH(Maximum,B2:NC2,0))
      • Clive_Rabson's avatar
        Clive_Rabson
        Brass Contributor
        May 10, 2024
        Detlef
        Thanks but again that gives the result: #NAME?

Resources