Skip to main content
Solved

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

  • March 8, 2022
  • 4 replies
  • 47 views
V
Forum|alt.badge.img+11

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?

Best answer by Mohamed_Swella1

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:

  1. One formula column each for the three age categories.
  2. 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"))))))))))))

4 replies

Mohamed_Swella1
Forum|alt.badge.img+17

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"))))
V
Forum|alt.badge.img+11
  • Valerie_Miles
  • Author
  • Participating Frequently
  • March 8, 2022

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:

  1. One formula column each for the three age categories.
  2. 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})))
Mohamed_Swella1
Forum|alt.badge.img+17

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:

  1. One formula column each for the three age categories.
  2. 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"))))))))))))
V
Forum|alt.badge.img+11
  • Valerie_Miles
  • Author
  • Participating Frequently
  • March 8, 2022

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.

Mohamed_Swella1
Terms & ConditionsAccessibility statement