Forum Discussion

Ntate209's avatar
Ntate209
Copper Contributor
Jul 01, 2022

Help, I'm in Formula Quicksand

I've been trying to work on this formula and I just can't seem to get it right.  I'm using an Ifs And formula where some of the answers come back correctly whereas others don't.  If anyone could help...
excel
mtarler's avatar
mtarler
Silver Contributor
Jul 01, 2022
I don't even want to try to work through that crazy logic. You need to try to group them better, use nesting instead of multiple cases of doing the same comparisons and try to clean it up. BUT let me ask, is this question based on your equation having a firm basis (each conditional is based on some actual rule) OR are you just trying to CREATE some equation that will produce the correct answer in each case? If it is the latter I can see a more simple solution I believe:
If MIN non-zero is in middle & < MAX then = middle # else = MIN non-zero -5
=LET(in,A3:C3,minNONzero, XLOOKUP(1,SORT(in),SORT(in),,1), IF((minNONzero=B3)*(minNONzero<MAX(in)),B3,minNONzero-5))
mtarler's avatar
mtarler
Silver Contributor
Jul 01, 2022

mtarler  And if you want to do it using a dynamic array formula instead of filling down:

=LET(input,A3:C17, BYROW(input,LAMBDA(in, LET(
       minNONzero, XLOOKUP(1,SORT(in),SORT(in),,1), 
       minNONzero-IF((minNONzero=INDEX(in,2))*(minNONzero<MAX(in)),0,5)))))

both are in the attached

Resources