NESTED IF and AND formula help please

[Pam Laidler]

Hi Excel Gurus 

Back in November I found some answers to my queries here in the forum, however between then and now I've managed to mess the formulas up.  I went back and copied the previous formula but its not working now.
I've made up a small spreadsheet with the formulas I have, but it doesn't seem to recognise TM as a true statement.  I've tried changing the row 6 fields from percentage to general but it still doesn't work. 
Any help would be most appreicated. 

Pam 

[#2]

@Pam Laidler

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.

[#3]

@Hans Vogelaar  thanks Hans I'll try that. :) 

[#4]

@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 

[#5]

@Pam Laidler 

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.

[#6]

@Hans Vogelaar 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 

[#7 · Accepted solution]

@Pam Laidler 

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".

[#8]

@Hans Vogelaar  Thanks Hans - hopefully this will give me what I need to get this formula correct. 

I really appreciate you Excel Gurus being there. 

Pam