Position of the first number in a list lower than n

p45cal

p45cal

Super Moderator
Staff member
Joined
Dec 16, 2012
Messages
2,319
Reaction score
40
Points
48
Excel Version(s)
365
If I have:
Code: 
let
    Source = {7,7,6,4,7,3,4,4,5,1},
    Posn = List.PositionOf(Source,4)
in
    Posn
I get 3.
What I really want is (air code)
Code: 
    Posn = List.PositionOf(Source,<6)
that is the position of first occurence of a value less than 6 (the return being 3).
How?

Ancillary question: Can I do it from the end of the list instead of from the beginning, ie. the last number less than 6 in the list?
 
This should do it

Code: 
     Posn = List.PositionOf(Source, List.FirstN(List.Select(Source, each _ < 6), 1){0})

If you want from the end, reverse the list, List.Reverse
 
Prompted to play around further,
Code: 
Posn=List.PositionOfAny(List.Transform(Source,each _ < 6),{true},0)
copes with possibilities of negative numbers and decimal numbers and means no arithmetic needed to find the last position in the original list having found the position in a reversed list.
Is it heavier on resources, or slow, or bad practice?

I had looked at PositionOfAny but mistakenly thought the 3rd argument was the 1st, 2nd, 3rd, 4th occurence, not an Occurrence.Type of first, last and all.
Where can I get something like intellisense as in Bill's picture? When writing formulae in a spreadsheet cell you get prompted:
2020-10-15_122143.png
as well as in the VBE.
 
Last edited:
Bill Szysz said:
There is a function List.PositionOfAny. See the picture below.
Click to expand...

Bill, your picture is missing. I accidentally deleted this post (I was deleting the posts of some idiot who posted the same post to every forum), and although I undeleted it I think that caused the image to go. Could you be so good a to re-post it?
 
p45cal said:
Prompted to play around further,
Code: 
Posn=List.PositionOfAny(List.Transform(Source,each _ < 6),{true},0)
copes with possibilities of negative numbers and decimal numbers and means no arithmetic needed to find the last position in the original list having found the position in a reversed list.
Click to expand...

A better way in my original suggestion would be to use List.LastN rather than reverse it as I suggested (although I also agree Bill's suggestion of List.PositionOfAny is better).
 
Last edited:
Thanks for that Bill.

Your 'intellisense' is a lot better than the documentation, which describes the occurrence argument as

occurrence: The maximum number of occurrences that can be returned.
 
You must log in or register to reply here.
Back
Top