Forum Discussion
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 I would really appreciate it. A snapshot of the columns are below, the green shows what the answer should be. Here is my formula IFS(AND($A3<=0,$B3>=$C3),($C3-5),IF($A3<=0,$B3>=1,$B3<$C3),$B3,IF($B3<=0,$A3>=$C3),($C3-5),IF($B3<=0,$A3=$C10),($C3-5),IF($A3<=0,$B3<=0),($C3-5),IF($A3>=1,$B3>$A3,$B3>$C3),($C3-5),IF($A3>=1,$B3>$A3,$A3>=$C3),($C3-5),IF($A3>=1,$B3>=$A3,$A3<$C3),($A3-5),IF($B3>=1,$B3<$A3,$A3>=$C3),($C3-5),IF($B3>=1,$B3<$A3,$A3<$C24),$B3,IF($C3=$A3,$A3=$C3),($C3-5))
7 Replies
- 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 First let me say thank you so much for trying to help me! I must say, as I'm sure you can tell, this is over my head! To try to answer your question, we are trying to find the lowest unit rate which is column B but the outcome cannot be higher than or equal to the number in column B or C.
- This doesn't probably isn't 'over your head' you just need to learn a little more about excel and take it one bite at a time. So excel offers many functions and options and we just need to know which ones to use. To figure that out, we need to understand the logic you need to perform. You say you are, "trying to find the lowest unit rate which is column B but the outcome cannot be higher than or equal to the number in column B or C." So I assume that the -5 is to make it not "higher than or equal to" and I assume you mean column A or C (not B or C). I also assume that a value of 0 should be ignored. That all said, I believe my logic was correct. So what I did is:
=LET(in,A3:C3,
minNONzero, XLOOKUP(1,SORT(in),SORT(in),,1),
IF((minNONzero=B3)*(minNONzero<MAX(in)),B3,minNONzero-5))
Line 1 set "in" to be the input row,
Line 2 then found the smallest value >0 in that set
Line 3 then checks if the smallest non-zero number (minNONzero) is the middle number (B3) AND less than the maximum of the 3 numbers and if so return the middle number (B3) and if NOT then return that minNONzero number - 5.
So please note lines 10 and 12 in your data (at least I think those are the line #s):
50 50 50 -> 45
60 50 50 -> 50
so in the 1st case because the min number = col A and col C the correct value is 45
the 2nd case is the tricky one because col B and C are equal BUT col A is bigger. In this case you say the correct value is 50. If this is not correct then I could change the formula/logic accordingly
