Hi @Valerie_Miles ,
There are several reasons the formula is not working.
First of all, you cannot use “&” for the IF Statement unless you are meaning to concatenate the results (which I dont think you are?).
The formula should look like this instead
IF(AND({PVSA Age Group}="Teens",{Total PVSA Hrs}<50),"No Award",
(IF(AND({PVSA Age Group}="Teens",{Total PVSA Hrs}<=74),"Bronze",
IF(AND({PVSA Age Group}="Teens",{Total PVSA Hrs}<=99),"Silver",
"Gold"))))
Hi @Valerie_Miles ,
There are several reasons the formula is not working.
First of all, you cannot use “&” for the IF Statement unless you are meaning to concatenate the results (which I dont think you are?).
The formula should look like this instead
IF(AND({PVSA Age Group}="Teens",{Total PVSA Hrs}<50),"No Award",
(IF(AND({PVSA Age Group}="Teens",{Total PVSA Hrs}<=74),"Bronze",
IF(AND({PVSA Age Group}="Teens",{Total PVSA Hrs}<=99),"Silver",
"Gold"))))
You are absolutely correct. I did not want to concatenate. Thank you. How to add (not an &!) the condition if the PVSA Age Group is “Young Adults” or “Adults”? The other formulas to be part of the decision are:
for Young Adults:
IF(AND({PVSA Age Group}=“Young Adults",{Total PVSA Hrs}<100),”No Award",
(IF(AND({PVSA Age Group}=“Young Adults”,{Total PVSA Hrs}<=174),”Bronze",
IF(AND({PVSA Age Group}=“Young Adults",{Total PVSA Hrs}<=249),”Silver",
“Gold”))))
and for Adults:
IF(AND({PVSA Age Group}=“Adults”,{Total PVSA Hrs}<100),”No Award",
(IF(AND({PVSA Age Group}=“Adults”,{Total PVSA Hrs}<=249),”Bronze",
IF(AND({PVSA Age Group}=“Adults”,{Total PVSA Hrs}<=499),”Silver",
“Gold”))))
How to nest these IF statements with the one you polished up for me? Thank you, I will Venmo you coffee money!
My CURRENT INEFFICIENT WORK AROUND which drives me nuts is:
- One formula column each for the three age categories.
- A fourth column for “Award based on Age” with this formula:
IF({PVSA Age Group}=“Teens”,{Teen Award Level},(IF({PVSA Age Group}=“Young Adults”,{Young Adults Award Level},{Adults Award Level})))
You are absolutely correct. I did not want to concatenate. Thank you. How to add (not an &!) the condition if the PVSA Age Group is “Young Adults” or “Adults”? The other formulas to be part of the decision are:
for Young Adults:
IF(AND({PVSA Age Group}=“Young Adults",{Total PVSA Hrs}<100),”No Award",
(IF(AND({PVSA Age Group}=“Young Adults”,{Total PVSA Hrs}<=174),”Bronze",
IF(AND({PVSA Age Group}=“Young Adults",{Total PVSA Hrs}<=249),”Silver",
“Gold”))))
and for Adults:
IF(AND({PVSA Age Group}=“Adults”,{Total PVSA Hrs}<100),”No Award",
(IF(AND({PVSA Age Group}=“Adults”,{Total PVSA Hrs}<=249),”Bronze",
IF(AND({PVSA Age Group}=“Adults”,{Total PVSA Hrs}<=499),”Silver",
“Gold”))))
How to nest these IF statements with the one you polished up for me? Thank you, I will Venmo you coffee money!
My CURRENT INEFFICIENT WORK AROUND which drives me nuts is:
- One formula column each for the three age categories.
- A fourth column for “Award based on Age” with this formula:
IF({PVSA Age Group}=“Teens”,{Teen Award Level},(IF({PVSA Age Group}=“Young Adults”,{Young Adults Award Level},{Adults Award Level})))
It’s always a pleasure, glad it worked as intended.
The idea of nested IFs is that the FALSE value is also an IF statement. so basically after the TRUE value you just open another IF statement, but it gets complicated with all the brackets. What I personally do is write each Argument on a separate line and then count them :grinning_face_with_big_eyes:
This full formula should be as follows
IF(AND({PVSA Age Group}="Teens",{Total PVSA Hrs}<50),"No Award",
IF(AND({PVSA Age Group}="Teens",{Total PVSA Hrs}<=74),"Bronze",
IF(AND({PVSA Age Group}="Teens",{Total PVSA Hrs}<=99),"Silver",
IF({PVSA Age Group}="Teens", "Gold",
IF(AND({PVSA Age Group}="Adults",{Total PVSA Hrs}<100),"No Award",
IF(AND({PVSA Age Group}="Adults",{Total PVSA Hrs}<=249),"Bronze",
IF(AND({PVSA Age Group}="Adults",{Total PVSA Hrs}<=499),"Silver",
IF({PVSA Age Group}="Adults","Gold",
IF(AND({PVSA Age Group}="Young Adults",{Total PVSA Hrs}<100),"No Award",
IF(AND({PVSA Age Group}="Young Adults",{Total PVSA Hrs}<=174),"Bronze",
IF(AND({PVSA Age Group}="Young Adults",{Total PVSA Hrs}<=249),"Silver",
IF({PVSA Age Group}="Young Adults","Gold"))))))))))))
It’s always a pleasure, glad it worked as intended.
The idea of nested IFs is that the FALSE value is also an IF statement. so basically after the TRUE value you just open another IF statement, but it gets complicated with all the brackets. What I personally do is write each Argument on a separate line and then count them :grinning_face_with_big_eyes:
This full formula should be as follows
IF(AND({PVSA Age Group}="Teens",{Total PVSA Hrs}<50),"No Award",
IF(AND({PVSA Age Group}="Teens",{Total PVSA Hrs}<=74),"Bronze",
IF(AND({PVSA Age Group}="Teens",{Total PVSA Hrs}<=99),"Silver",
IF({PVSA Age Group}="Teens", "Gold",
IF(AND({PVSA Age Group}="Adults",{Total PVSA Hrs}<100),"No Award",
IF(AND({PVSA Age Group}="Adults",{Total PVSA Hrs}<=249),"Bronze",
IF(AND({PVSA Age Group}="Adults",{Total PVSA Hrs}<=499),"Silver",
IF({PVSA Age Group}="Adults","Gold",
IF(AND({PVSA Age Group}="Young Adults",{Total PVSA Hrs}<100),"No Award",
IF(AND({PVSA Age Group}="Young Adults",{Total PVSA Hrs}<=174),"Bronze",
IF(AND({PVSA Age Group}="Young Adults",{Total PVSA Hrs}<=249),"Silver",
IF({PVSA Age Group}="Young Adults","Gold"))))))))))))
This works! And is much more elegant than my 4 extra columns for the single solution. I was so close. Thank you for the foundational knowledge that I can apply to future formulas. Now let me know how to Venmo you a coffee. Thank you very much Mohamed.