Forum Discussion

Dragon_Claw's avatar
Dragon_Claw
Copper Contributor
May 24, 2019

How to get farthest left value greater than 0?

I've got 31 columns (one for each day), not every day has a value, but I want to be given the date of the value that's the farthest left, how do I do this?
excel

3 Replies

  • Twifoo's avatar
    Twifoo
    Silver Contributor
    May 24, 2019
    You can try something like this formula:
    =INDEX(B2:AF2,
    AGGREGATE(15,6,1/(LEN(B2:AF2)>0)*(COLUMN(B2:AF2)-1),1))
    • Dragon_Claw's avatar
      Dragon_Claw
      Copper Contributor
      May 24, 2019
      I've never used aggregate or Len before, what's the cliffnotes on them?
      • Twifoo's avatar
        Twifoo
        Silver Contributor
        May 24, 2019
        You just have to enter the formula I suggested, then press F2 to enter edit mode and click the name of the function to obtain help on it. By the way, please let me know if the formula returns your desired result.

Resources