Home

How to get farthest left value greater than 0?

%3CLINGO-SUB%20id%3D%22lingo-sub-642508%22%20slang%3D%22en-US%22%3EHow%20to%20get%20farthest%20left%20value%20greater%20than%200%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-642508%22%20slang%3D%22en-US%22%3EI've%20got%2031%20columns%20(one%20for%20each%20day)%2C%20not%20every%20day%20has%20a%20value%2C%20but%20I%20want%20to%20be%20given%20the%20date%20of%20the%20value%20that's%20the%20farthest%20left%2C%20how%20do%20I%20do%20this%3F%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-642508%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-642573%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20get%20farthest%20left%20value%20greater%20than%200%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-642573%22%20slang%3D%22en-US%22%3EYou%20can%20try%20something%20like%20this%20formula%3A%3CBR%20%2F%3E%3DINDEX(B2%3AAF2%2C%3CBR%20%2F%3EAGGREGATE(15%2C6%2C1%2F(LEN(B2%3AAF2)%26gt%3B0)*(COLUMN(B2%3AAF2)-1)%2C1))%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-642599%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20get%20farthest%20left%20value%20greater%20than%200%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-642599%22%20slang%3D%22en-US%22%3EI've%20never%20used%20aggregate%20or%20Len%20before%2C%20what's%20the%20cliffnotes%20on%20them%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-642609%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20get%20farthest%20left%20value%20greater%20than%200%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-642609%22%20slang%3D%22en-US%22%3EYou%20just%20have%20to%20enter%20the%20formula%20I%20suggested%2C%20then%20press%20F2%20to%20enter%20edit%20mode%20and%20click%20the%20name%20of%20the%20function%20to%20obtain%20help%20on%20it.%20By%20the%20way%2C%20please%20let%20me%20know%20if%20the%20formula%20returns%20your%20desired%20result.%3C%2FLINGO-BODY%3E
Dragon_Claw
Occasional Contributor
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?
3 Replies
You can try something like this formula:
=INDEX(B2:AF2,
AGGREGATE(15,6,1/(LEN(B2:AF2)>0)*(COLUMN(B2:AF2)-1),1))
I've never used aggregate or Len before, what's the cliffnotes on them?
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.
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
30 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies