New formula field based on 6 fields --> just the lowest should be shown with a dedicated

[Daniel_Walter]

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"

[Sho]

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

[Sho]

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

[TheTimeSavingCo]

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

[Daniel_Walter]

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?

[Sho]

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})
)

[TheTimeSavingCo]

@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!

[Sho]

@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]

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