May 03 2022 07:13 AM
I am trying to create a formula to choose values based on a set of rules, given the following situation.
Column A = Value of 1,2 or 3
Column B = 4
Column C = 5
Column D = 6
Column E = 7
I am trying to figure out a formula i can use for column F which will enter the values from Columns A to E in the following priority:
If 4, enter 4. Otherwise enter 5 AND 6 OR 7 (when available). Otherwise enter 3,2 or 1.
Right now i have an IFS statement:
IFS(B1=4,4,C1=5,5,D1=6,6,E1=7,7,A1=3,3,A1=2,2,A1=1,1) which partially works but I'm having trouble getting the conditions for 5,6 and 7 met.
Any help would be appreciated.
Thanks,
Lisa
May 03 2022 07:44 AM
=IFS(B1=4,4,AND(C1=5,D1=6),56,E1=7,7,A1=3,3,A1=2,2,A1=1,1)
Does this formula return your expected result?
May 03 2022 08:19 AM - edited May 03 2022 08:43 AM
Sadly, it does not work for all cases.
The problem is I need it to return 5 and 6 or 5 and 7 (if both have values) and 5,6,7 if all 3 have values. Otherwise, just return 5 or 6 or 7 before moving on to returning 3/2/1.
This formula returns 56 when no data for 4 (even if data for 7) but if data for 5 and 7 but no data for 6 it returns just 7 instead of 5,7.
Also, if i have values for 1 and 6, it is returning 1 instead of 6 (same if a value for 1 and 5).
Does that make sense?
I went ahead and modified your formula to:
=IFS(B2=4,4,AND(C2=5,D2=6),56,AND(C2=5,E2=7),57,AND(C2=5,D2=6,E2=7),567,D2=6,6,C2=5,5,E2=7,7,A2=3,3,A2=2,2,A2=1,1)
This fixes the case with 1 and 6 / 1 and 5 and also when 5 and 7 but not when 5, 6 and 7. So very close ...
If i can't get that last instance to work, it may be ok but do you know if there is a way to return 5,6 instead of 56 with this formula?
May 03 2022 08:37 AM
=IFS(B1=4,4,AND(C1=5,D1=6,E1=7),567,AND(C1=5,D1=6),56,AND(C1=5,E1=7),57,C1=5,5,D1=6,6,E1=7,7,A1=3,3,A1=2,2,A1=1,1)
Maybe with this formula as shown in the attached file.
May 03 2022 09:07 AM