Using Ifs and Min together, returning a different number depending on lowest value in a cell

Question

I have a column that will contain several numbers (between 1-4) in each cell depending on answers from a survey. ie 1,2,3,4 or 3,4 or 1,2,3

In a separate column i need to have just one single number, which, because the answers escalate means that i just need the lowest value returned. Ie if 1,2,3,4, i need 1. If 3,4 i want to return 3.

From what i understand, I could do this easily enough using the MIN function. However due how the question was asked, if the responds lowest number was 1, I need to enter a 4 into the database, If 2 than 3, 3 than 2, and 4 than 1.

My thought was to use several IF statements that IF the MIN value = 1, than return 4 etc. However, I'm not sure if this is the most effective way to go about it.

Thanks for any help!!

Answer 1

TRIM function - Use TRIM on text that you have received from another application that may have irregular spacing.

For example, if " 1,2" was entered into a cell by mistake (with a space before 1), then after processing with the LEFT() function, we will get the correct string "1,2" (without extra space)

LEFT returns the first character or characters in a text string

So, LEFT("1,2";1) return "1" (as string)

VALUE function converts a text string that represents a number to a number.

Now subtract this number from 5: 5-1=4, 5-2=3, etc.

If nothing is entered in the cell, an error will occur. To avoid displaying an error, wrap the entire expression in an IFERROR function

=IFERROR(5-VALUE(LEFT(TRIM(A1);1));"")