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 

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.

Lorenzo_Masotti's avatar
Lorenzo_Masotti
Copper Contributor
Feb 12, 2023
Thank you Peter Here What I found trying the steps before.
Even I close the brackets, Excel says: " There are too many arguments", without specifies the discipline of them. I reported to you the second case where I used the multiplication factor instead of and. But in this case It says it contains an error.:
SE((6<=DG29)*(DG29<7);CX29=50);1310;SE((50<=CX29)*(DG29<55)*(7<DG29);DG29<=8;1225);SE((8< DG29)*(DG29<=9);CX29>45;1265;SE((10<DG29)*(DG29<=11);CX29<45;1305;SE((12<DG29)*(DG29<=13);CX29>45;1346;))))))))
  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Feb 12, 2023

    Lorenzo_Masotti 

    Sorry, not one error, many errors.

     

     

    =   IF(    ( 6 < x) * (x <=  7) * (v = 50),                  1310,
    IF(    ( 7 < x) * (x <=   * (50 <= v) * (v < 55),      1225,
    IF(    ( 8 < x) * (x <=  9) * (v > 45),                  1265,
    IF(    (10 < x) * (x <= 11) * (v < 45),                  1305,
    IF(    (12 < x) * (x <= 13) * (v > 45),                  1346, 
    FALSE)))))

     

     

    [Note: one of the lines is messed up by the editor]

    I tried to lay out your solution, correcting as I went, to see what you are trying to do.  Why is there no result for values of x between 9 and 10?

    IFS would improve the formula but I think laying is out as a table with x going up in ones down the rows and v changing in 5s across the top.  For a particular (x, v) match the headers to get the row and column indices and return the value using the INDEX function.

     

    If you are using the 365 that would allow you to use the IFS but XMATCH/INDEX would probably be better.  XLOOKUP could also be used.

     

  • Lorenzo_Masotti's avatar
    Lorenzo_Masotti
    Copper Contributor
    Feb 12, 2023
    Peter, I tried with the new subscription:
    =PIÙ.SE(E(CX29<= 50;DG29<7) DG29<=6;1310; E(CX29=>45;CX29<50;DG29<7) DG29 =<8;1225; (E(DG29>8; DG29<=9)CX29<=50;1265;(E(DG29>10; DG29<=11)CX29<=50;1305;E(DG29>12; DG29<=13) (CX29<=50;1346;"NO"))))) Actually is not working eather.. Any reason?

Resources