Skip to main content

The Goal is that I will have a new formula field, which gives me the lowest value of all 6, to be more precise a word which belongs to one of the dedicated fields (here it would be value "8" - field "1.6+2.6+3.6 traditio..." matching word which should be shown is "traditional")

Best case would be, I also have a second field with the second lowest value of all 6 (here it would be value "9" - field "1.5+2.5+3.5 traditio..." matching word should be "social"

How about this?

the lowest value:

MIN({Field 1}, {Field 2}, {Field 3}), {Field 4}, {Field 5}, {Field 6})

the second lowest value:

MIN( IF({Field 1} != MIN({Field 1}, {Field 2}, {Field 3}), {Field 4}, {Field 5}, {Field 6}), IF({Field 2} != MIN({Field 1}, {Field 2}, {Field 3}), {Field 4}, {Field 5}, {Field 6}), IF({Field 3} != MIN({Field 1}, {Field 2}, {Field 3}), {Field 4}, {Field 5}, {Field 6}), IF({Field 4} != MIN({Field 1}, {Field 2}, {Field 3}), {Field 4}, {Field 5}, {Field 6}), IF({Field 5} != MIN({Field 1}, {Field 2}, {Field 3}), {Field 4}, {Field 5}, {Field 6}), IF({Field 6} != MIN({Field 1}, {Field 2}, {Field 3}), {Field 4}, {Field 5}, {Field 6}) )

 This is a funny formula


Hm? I guess I was mistaken.
Where is the dedicated word input?


Hm? I guess I was mistaken.
Where is the dedicated word input?


Hmm, I think OP wants to show "traditional" with the lowest value, so maybe:

MIN({Field 1}, {Field 2}, {Field 3}), {Field 4}, {Field 5}, {Field 6}) & " traditional"

I can't think of an easy way to get the second lowest number though


Hey everyone,

so your approach @TheTimeSavingCo looks pretty good, but the problem is every field has a dedicated word which should appear if this field is the lowest.
for example:
Field 1= "Time" Field 2= "Day" Field 3= "break" .... Field 6= "traditional" and so on.
Is there any chance to tackle this?


I see that
Then how about this

Add a formula field named "MIN" and  formula:

 

MIN({Field 1}, {Field 2}, {Field 3}), {Field 4}, {Field 5}, {Field 6})

 

Then, put in a word for each field

 

IF({MIN}={Field 1}, {MIN} & " - " & "Field 1")& IF({MIN}={Field 2}, {MIN} & " - " & "Field 2")& IF({MIN}={Field 3}, {MIN} & " - " & "Field 3")& IF({MIN}={Field 4}, {MIN} & " - " & "Field 4")& IF({MIN}={Field 5}, {MIN} & " - " & "Field 5")& IF({MIN}={Field 6}, {MIN} & " - " & "Field 6")

 

SWITCH function would result in an error.

If the formula for MIN is this, you get the second lowest value.

 

MIN( IF({Field 1} != MIN({Field 1}, {Field 2}, {Field 3}), {Field 4}, {Field 5}, {Field 6}), IF({Field 2} != MIN({Field 1}, {Field 2}, {Field 3}), {Field 4}, {Field 5}, {Field 6}), IF({Field 3} != MIN({Field 1}, {Field 2}, {Field 3}), {Field 4}, {Field 5}, {Field 6}), IF({Field 4} != MIN({Field 1}, {Field 2}, {Field 3}), {Field 4}, {Field 5}, {Field 6}), IF({Field 5} != MIN({Field 1}, {Field 2}, {Field 3}), {Field 4}, {Field 5}, {Field 6}), IF({Field 6} != MIN({Field 1}, {Field 2}, {Field 3}), {Field 4}, {Field 5}, {Field 6}) )

 

 


I see that
Then how about this

Add a formula field named "MIN" and  formula:

 

MIN({Field 1}, {Field 2}, {Field 3}), {Field 4}, {Field 5}, {Field 6})

 

Then, put in a word for each field

 

IF({MIN}={Field 1}, {MIN} & " - " & "Field 1")& IF({MIN}={Field 2}, {MIN} & " - " & "Field 2")& IF({MIN}={Field 3}, {MIN} & " - " & "Field 3")& IF({MIN}={Field 4}, {MIN} & " - " & "Field 4")& IF({MIN}={Field 5}, {MIN} & " - " & "Field 5")& IF({MIN}={Field 6}, {MIN} & " - " & "Field 6")

 

SWITCH function would result in an error.

If the formula for MIN is this, you get the second lowest value.

 

MIN( IF({Field 1} != MIN({Field 1}, {Field 2}, {Field 3}), {Field 4}, {Field 5}, {Field 6}), IF({Field 2} != MIN({Field 1}, {Field 2}, {Field 3}), {Field 4}, {Field 5}, {Field 6}), IF({Field 3} != MIN({Field 1}, {Field 2}, {Field 3}), {Field 4}, {Field 5}, {Field 6}), IF({Field 4} != MIN({Field 1}, {Field 2}, {Field 3}), {Field 4}, {Field 5}, {Field 6}), IF({Field 5} != MIN({Field 1}, {Field 2}, {Field 3}), {Field 4}, {Field 5}, {Field 6}), IF({Field 6} != MIN({Field 1}, {Field 2}, {Field 3}), {Field 4}, {Field 5}, {Field 6}) )

 

 


@Sho   Hmm, I think you might have accidentally pasted a different set of code in your message?  I tried the code to get the second lowest value but it did not output the expected value

I think I understand what you are doing though, and think that your original code may have looked like this:

MIN( IF( {Field 1} = MIN({Field 1}, {Field 2}, {Field 3}, {Field 4}, {Field 5}, {Field 6}), MIN({Field 2}, {Field 3}, {Field 4}, {Field 5}, {Field 6}) ), IF( {Field 2} = MIN({Field 1}, {Field 2}, {Field 3}, {Field 4}, {Field 5}, {Field 6}), MIN({Field 1}, {Field 3}, {Field 4}, {Field 5}, {Field 6}) ), IF( {Field 3} = MIN({Field 1}, {Field 2}, {Field 3}, {Field 4}, {Field 5}, {Field 6}), MIN({Field 2}, {Field 1}, {Field 4}, {Field 5}, {Field 6}) ), IF( {Field 4} = MIN({Field 1}, {Field 2}, {Field 3}, {Field 4}, {Field 5}, {Field 6}), MIN({Field 2}, {Field 3}, {Field 1}, {Field 5}, {Field 6}) ), IF( {Field 5} = MIN({Field 1}, {Field 2}, {Field 3}, {Field 4}, {Field 5}, {Field 6}), MIN({Field 2}, {Field 3}, {Field 4}, {Field 1}, {Field 6}) ), IF( {Field 6} = MIN({Field 1}, {Field 2}, {Field 3}, {Field 4}, {Field 5}, {Field 6}), MIN({Field 2}, {Field 3}, {Field 4}, {Field 5}, {Field 1}) ) )

This seems to output the expected value

I would not have thought of this method without your example code, thank you!


@TheTimeSavingCo 

Thanks for pointing this out.

It appears that a mistake was made when changing from the test code to the explanatory code.
It should now work!

MIN( IF({Field 1} != MIN({Field 1}, {Field 2}, {Field 3}, {Field 4}, {Field 5}, {Field 6}), {Field 1}), IF({Field 2} != MIN({Field 1}, {Field 2}, {Field 3}, {Field 4}, {Field 5}, {Field 6}), {Field 2}), IF({Field 3} != MIN({Field 1}, {Field 2}, {Field 3}, {Field 4}, {Field 5}, {Field 6}), {Field 3}), IF({Field 4} != MIN({Field 1}, {Field 2}, {Field 3}, {Field 4}, {Field 5}, {Field 6}), {Field 4}), IF({Field 5} != MIN({Field 1}, {Field 2}, {Field 3}, {Field 4}, {Field 5}, {Field 6}), {Field 5}), IF({Field 6} != MIN({Field 1}, {Field 2}, {Field 3}, {Field 4}, {Field 5}, {Field 6}), {Field 6}) )

 


@TheTimeSavingCo 

Thanks for pointing this out.

It appears that a mistake was made when changing from the test code to the explanatory code.
It should now work!

MIN( IF({Field 1} != MIN({Field 1}, {Field 2}, {Field 3}, {Field 4}, {Field 5}, {Field 6}), {Field 1}), IF({Field 2} != MIN({Field 1}, {Field 2}, {Field 3}, {Field 4}, {Field 5}, {Field 6}), {Field 2}), IF({Field 3} != MIN({Field 1}, {Field 2}, {Field 3}, {Field 4}, {Field 5}, {Field 6}), {Field 3}), IF({Field 4} != MIN({Field 1}, {Field 2}, {Field 3}, {Field 4}, {Field 5}, {Field 6}), {Field 4}), IF({Field 5} != MIN({Field 1}, {Field 2}, {Field 3}, {Field 4}, {Field 5}, {Field 6}), {Field 5}), IF({Field 6} != MIN({Field 1}, {Field 2}, {Field 3}, {Field 4}, {Field 5}, {Field 6}), {Field 6}) )

 


Oooh, that's really nice and is even cleaner than what I thought you were doing!