Forum Discussion

Pam Laidler's avatar
Pam Laidler
Copper Contributor
Apr 26, 2023
Solved

NESTED IF and AND formula help please

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 

 

Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    Apr 30, 2023

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

     

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Apr 26, 2023

    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.

      • Pam Laidler's avatar
        Pam Laidler
        Copper Contributor
        Apr 30, 2023

        @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 

Resources