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!


Reply