Jul 01 2022 10:51 AM
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))
Jul 01 2022 12:26 PM
Jul 01 2022 12:38 PM
@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
Jul 05 2022 04:45 AM
@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.
Jul 05 2022 06:35 AM
Jul 05 2022 07:11 AM
Jul 05 2022 08:09 AM
@Ntate209 so I believe that makes things easier as you just need the min >0 number when you -5 to columns A and C. Try this:
=XLOOKUP(1,SORT(A3:C3-{5,0,5}),SORT(A3:C3-{5,0,5}),,1)
In the attached I updated the formula and updated the dynamic array version also.