Forum Discussion
NESTED IF and AND formula help please
Yes - a text value is greater than ANY number, not just 0.
So if D6 equals "TM", the formula
=IFS(D6>1, "Greater than 1", D6="TM", "Equals TM")
will return "Greater than 1".
The problem is that Excel treats text values as greater than all numbers (including 0), so "TM">0.
Solution: switch the two parts in IFS. In D7:
=IFERROR(IFS(D6="TM",5*6,D$6>0,B7*$D$6),0)
Fill to the right.
HansVogelaar thanks Hans I'll try that. 🙂
@Hans Vogelaar That worked however the formula is longer than what indicated and I thought I'd be able to extend it - however still need help please
How can I say
If E6=TM and D6=TM do this
If E6=TM and D6 is a %figure then do this
If E6 = %figure then B7 multiplied by E6
I've got the following:
IFERROR(IFS(AND(E$6="TM",$D$6="TM"),CALCULATOR!$I18*CALCULATOR!$M15/CALCULATOR!$K15-$D7,AND(E$6="TM",$D$6>0),CALCULATOR!$I18*CALCULATOR!$M15/CALCULATOR!$K15),E$6>0,B7*E$6,0)
But will the second argument won't work as you've said that it takes TM as a value greater than 0 - so will the second argument then still the give the answer as per the first argument?. Is there a way of saying, if the cell = a percentage figure or any type of figure as opposed to alphabet letters.
The % figure in row 6 could be anything from 10% to 90%
the cells D6 to f6 are populated from another worksheet within the same spreadsheet, which has its answers selected from a lookup table, so I want to automate things as much as possible without getting myself tied in knots! The parameters have changed over the years of us using this spreadsheet and what use to work now doesn't. (nothing stays the same)Thanks
Pam
IFS evaluates the conditions from left to right, and it stops when a condition evaluates to TRUE.
So if E6 and D6 both equal "TM", the formula will return the first value_if_true, and it won't test whether E6>0 or D6>0.
And if E6 equals "TM" and D6 contains a positive number, the formula will return the second value_if_true, and it won't test whether E6>0.
HansVogelaar Thanks Hans for your answers. I wonder if you could answer one more question please.
I was wondering if I said if D6>1 would that then stop D6=TM being a true answer?
Thanks
Pam