Feb 11 2023 06:10 AM
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 how to conclude the formula, because the equal sign, after minor/major also other unspecified problems don't convert my cells in the right values " TRUE") which I 've written on Excel.
Do you have any answer?
Best Regards,
Lorenzo Masotti
Feb 11 2023 08:46 AM
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.
Feb 11 2023 10:05 AM
Feb 11 2023 11:54 AM
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.
Feb 11 2023 12:59 PM
Feb 11 2023 02:29 PM
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.
Feb 12 2023 12:04 AM
Feb 12 2023 01:56 AM
Feb 12 2023 02:35 AM - edited Feb 12 2023 02:44 AM
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.