# Formula to return a result based on two variables (with ranges) in a chart

I am trying to automate the calculation of an award based on an age category (“PVSA Age Group”) and a number of hours volunteered (“Total PVSA Hrs.”) (see chart). I think I need nested IF statements with an AND, but not sure. It’s not working so far (because I have a day job hello.) I need an output based on these two variables. Here is the first part of my formula, attacking only the second row (we have no contenders for KID category in the first row, just the remaining 3 rows):
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”

That doesn’t work. Once I get it working, I need to figure out how to concatenate the other two rows (for different age categories.). Do I eliminate the subsequent AND statements, to save them for the concatenation?

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:

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”))))

IF(AND({PVSA Age Group}=“Adults”,{Total PVSA Hrs}<100),”No Award",

“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:

1. One formula column each for the three age categories.
2. A fourth column for “Award based on Age” with this formula:

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 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",