Forum Discussion
IFS only pulling one of three criteria
=IFS(D6>=99,50, D6>=74,40,D6>=49,25,TRUE,0)
The IFS formula returns the first result for which the condition is TRUE. For the value 99 condition D6>=99 is true therefore result is 50. For the value 74 condition D6>=74 is true therefore result is 40 and so on.
=IFS(D6>=99,50, D6>=74,40,D6>=49,25,TRUE,0)
The IFS formula returns the first result for which the condition is TRUE. For the value 99 condition D6>=99 is true therefore result is 50. For the value 74 condition D6>=74 is true therefore result is 40 and so on.
=IFS(AND(C6=Data!B6),D6>=99,50, D6>=74,40,D6>=49,25,TRUE,0)
=IFS(AND(C6=Data!B6,D6>=99),50, D6>=74,40,D6>=49,25,TRUE,0)
Yes it's possible. Above formula returns the intended result in my sheet. I only changed the position of the closing bracket of the AND function. AND includes logical C6=Data!B6 and logical D6>=99 and the bracket is wrapped around both logicals.
- Thanks again, it looks like that fixed it, but doesn't remove the number when Data!B6 is not selected. Do I have to add a TRUE or FALSE to the AND section?
If conditions C6=Data!B6 and D6>=99 are true then the formula returns 50 even if C6 and Data!B6 are empty.
If you only want to return 50 if conditions C6=Data!B6 and D6>=99 and 'Data!B6 isn't empty' are true then you can apply this formula.
=IFS(AND(C6=Data!B6,D6>=99,NOT(ISBLANK(Data!B6))),50, D6>=74,40,D6>=49,25,TRUE,0)
or
=IFS(AND(C6=Data!B6,D6>=99,Data!B6<>""),50, D6>=74,40,D6>=49,25,TRUE,0)
NOT(ISBLANK(Data!B6)) and Data!B6<>"" are formulas which return the same result.