Forum Discussion
Cableguy918
Dec 20, 2023Copper Contributor
IFS only pulling one of three criteria
Having trouble getting IFS function to read the the options I have listed and for some reason it is only acknowledging this first. I'm not getting errors, just pulls the first option for all three va...
- Dec 20, 2023
=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.
OliverScheurich
Dec 20, 2023Gold Contributor
=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.
- Cableguy918Dec 20, 2023Copper ContributorWould it then be possible to use AND in this formula?
=IFS(AND(C6=Data!B6),D6>=99,50, D6>=74,40,D6>=49,25,TRUE,0)- OliverScheurichDec 20, 2023Gold Contributor
=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.
- Cableguy918Dec 20, 2023Copper ContributorThanks 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?
- Cableguy918Dec 20, 2023Copper ContributorThank you so much, that is perfect!