View original (French)

SI(AND()) formula issue in Calc

Solved
Tilili -  
 Tilili -
Hello,

I have a problem with a formula in Calc and I can't find my mistake.

Here is the formula:
=IF(AND(F5>=50,H5<=21),"V","NV",IF(AND(F5<40,H5<=15),"V","NV"))

When the formulas are separated, they work but not together.

Thank you for your help.

1 réponse

Réponse 1 / 1
Debleir Posted messages 9 Status Membre
 
Hello,

I think you need to use an OR function.

In your case, it would look like this:

=IF(OR(AND(F5>=50,H5<=21),AND(F5<40,H5<=15)),"V","NV")

I don't have the software on hand, so I can't test it, but it should work if I understood what you're trying to do correctly...
0
Tilili
 
Thank you very much, that's great, it works.
However, if I want to add an additional condition (e.g. If F5>=40 but less than 50; and H5<=18) how should I transform the formula?
Actually, it's: - those under 40 must do 15 minutes or less
- between 40 and 49 years old, they must do 18 minutes or less
- over 50 years old, they must do 21 minutes or less

Thanks again.
0
Debleir Posted messages 9 Status Membre > Tilili
 
I don't quite understand what you want in "output"

In cases like these, I prefer to use nested IF() structures.

For example, we're going to create a function that works step by step:

First, it will check if F5>50 and H5<21, if that's the case it will return "V".
If not, it moves to the next step where it checks if F5<40 and H5<=15, again if that's good, it returns "V", otherwise if that's not the case, it moves to the last step which checks if H5<=18. If that's good, it returns "V", otherwise it returns "NV".
We've handled all cases since those over 50 have been checked first, those under 40 second, and the others (those between 40 and 49) are handled last.

Basically, it would look something like this:

IF(AND(F5>=50;H5<21);"V";IF(AND(F5<40;H5<=15);"V";IF(H5<=18;"V";"NV")))

Well, I still don't have the software to check, so I might have left out or forgotten a parenthesis, but the important thing is to understand the structure :)
0
Tilili > Debleir Posted messages 9 Status Membre
 
I need to create a formula to determine if different individuals have validated their running time, thus the V for validated and NV for not validated. However, they have a time limit based on their age.

- Under 40 years old must complete in 15 minutes or less
- Between 40 and 49 years old, they must complete in 18 minutes or less
- Over 50 years old must complete in 21 minutes or less

The hitch in the formula lies with those aged 40 to 49 who must complete in 18 minutes or less. In your formula, I do not see this age criterion.
0
Debleir Posted messages 9 Status Membre > Tilili
 
I made a mistake in my reasoning...
If we don't take age into account in the last case, we let many cases pass.

So it would rather give,

IF(AND(F5>=50,H5<=21),"V",IF(AND(F5>=40,H5<=18),"V",IF(AND(F5<40,H5<=15),"V","NV")))

Sorry, I hope you can follow me!
0
Tilili > Debleir Posted messages 9 Status Membre
 
No, it's me, I didn't express myself well in my reasoning. I'm not very good with formulas when they get a bit complicated.
But in any case, I've just tried the formula and it works. Thank you very much for your help. I thought I would never manage.
Thank you.
0