Solved: Re: Min value not in a list? - Page 2

Mic_L_Angelo · 02-06-2020 03:50 PM

Hey good folks.

I need help writing an expression that will give me the smallest value not in a list (if that’s even possible

I have a Table called “PINPOINTS”, which has a column called “Geotag Label”
The “Geotag Label” column has all number values (1,2,3, etc…)

What I’d like is for the initial 'Geotag Label" value to be the smallest number not currently in the list.

This way, if i delete value “56” from the Label column, the next row i add will return value “56”

Something like
"Between MAX(The Pinpoints[Geotag Label]) & MIN(The Pinpoints[Geotag Label]),
give me MIN(NOT(IN(The Pinpoints[Geotag Label]))),
else gimme MAX(The Pinpoints[Geotag Label] + 1) "

(bad formula, i know. but just an example of what i need)

Can anyone offer any ideas?

Steve

If you’d prefer to fill in the gaps from below, the alternative for that is:

Virtual column Geotag Label + 1: ([Geotag Label] + 1)

Available value:

ANY(
  SORT(
    LIST(1)
    + The Pinpoints[Geotag Label + 1]
    - The Pinpoints[Geotag Label]
  )
)

View solution in original post

Mic_L_Angelo

@Steve Hey folks,

So i did some more troubleshooting and looks like your second suggestion does work. I ran some scenarios and it eventually returned the minimum value absent from the sheet. I marked it as the solution.

Oddly, It seems that it wouldn’t work until i synced my app twice (or maybe it’s a time delay thing, formula taking a while to compute?)

In any case, I’m good from here. Thanks so much for your help and s/o to everyone else for offering their thoughts. I’m grateful there’s a community here willing to help everyone out.