# Re: New formula field based on 6 fields --&gt; just the lowest should be shown with a dedicated

1658 1
cancel
Showing results for
Did you mean:
4 - Data Explorer

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"

8 Replies 8
11 - Venus

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

11 - Venus

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

18 - Pluto

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

4 - Data Explorer

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?

11 - Venus

I see that

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

18 - Pluto

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

11 - Venus

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

18 - Pluto

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