Forum Discussion

Lorenzo_Masotti's avatar
Lorenzo_Masotti
Copper Contributor
Feb 11, 2023

IF( major to minor range values)

 Dear Mrs, I used the formula IF ( italian SE) to order the temperatures values so if different values come from first column(theta,r,w) I will have a specific theta,f. But I dind't find clear ho...
Excel for web
Formulas and Functions
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Feb 11, 2023

Lorenzo_Masotti 

The error is in using expressions like

= 11<value<=12

to determine whether a value lies between 11 and 12.  It is evaluated left to right with

= 11<value

returning TRUE or FALSE.  Either way

= TRUE<=12 or

= FALSE<=12

evaluates to FALSE.

 

Your test could be

= AND(11<value, value<=12)

or, somewhat more obscure,

= XOR(value<={11,12})

 

IFS would be better than nested IF statements and a table lookup would be better again.

Lorenzo_Masotti's avatar
Lorenzo_Masotti
Copper Contributor
Feb 11, 2023
Thank you Peter, I think with Office 2016 I cannot try IFS. So I'm trying to use the nasted option. If you use AND, which mode to list the logical parameters is right with and/or and if? for Example let say I use your advice := AND(11<value, value<=12)

IF(AND(DG29<7,DG29=>6;CX29=50);1310;IF(AND(CX29>50,CX29<=55;DG29>7,DG29<8;1225);IF(AND(DG29<=9,DG29>8);1265;IF(AND(DG29>10,Dg29<=11;1305;IF(AND(DG29>12,dg29<=13);1346;"NO")))))
But In this formula I noticed that, out from the first bracket parameters,all cells are made by the grey/black color which means not correct order. Which is the right manner to dispone IF and E together without IFS?

I written IF(AND(1condition,2cond;3cond); 1310 should be recognizable
IF( 1 condition, AND(2,3 condition); IF true; IF False/NEW IF would not.

Did I have to buy new Office? I ll do it. Thank you Peter
  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Feb 11, 2023

    Lorenzo_Masotti 

    Since you plan to use the nested IFs, bear in mind that the AND function needs to be closed

    = IF(AND(DG29<7;DG29=>6);CX29=50; ... )

Alternatively
= IF((DG29<7)*(DG29=>6);CX29=50; ... )

    and you need to use the ";" as a separator as you do elsewhere.  You could also use multiplication as an equivalent to AND, since a product evaluates to 0 (i.e. FALSE) if either term is zero.  In the main, your criteria do not need two terms because, simply getting to evaluate a condition implies that part of the condition is satisfied since it failed the previous test.

     

    As for a new Office, I would recommend 365 at least until the pace of change settles down.  It is backward compatible so it will run legacy solutions but its strength lies in the completely new methods that it supports.  My normal solutions bear so little resemblance to traditional Excel that I am probably regarded as completely insane by the majority on the forum!  I would stop using spreadsheets completely if I ever had to go back.

    • Lorenzo_Masotti's avatar
      Lorenzo_Masotti
      Copper Contributor
      Feb 11, 2023
      I now settle down the 365 Office Annual. Did you meanfor spreadsheets, for example, Libre Office and similar? ( I collected in fact various bugs or problems).
      • PeterBartholomew1's avatar
        PeterBartholomew1
        Silver Contributor
        Feb 11, 2023

        Lorenzo_Masotti 

        I just use the desktop version because, for me, the use of defined names is essential.  I work with dynamic array formulas all the time and almost all of my formulas involve LET and LAMBDA.

        Use what you know and take gentle steps to get your day job done, but read up and practice the new methods when you can.

Resources