Help on IF Formula - Multiple Statements

Brittany Pair

Hi!

I need some help on a IF Formula statement. On my sheet, I have a column that is using this equation -

=IF([Total ALL Athletes and Unified Partners]@row = 0, "0", IF([Total ALL Athletes and Unified Partners]@row <= 5, "1", IF([Total ALL Athletes and Unified Partners]@row <= 74, "2", IF([Total ALL Athletes and Unified Partners]@row <= 149, "3", IF([Total ALL Athletes and Unified Partners]@row <= 224, "4", IF([Total ALL Athletes and Unified Partners]@row <= 299, "5", IF([Total ALL Athletes and Unified Partners]@row <= 374, "6", IF([Total ALL Athletes and Unified Partners]@row <= 450, "7"))))))))

Basically, this formula is calculating a series of IF statements based a total from another column. In my example, the total number of athletes and partners will decide the number of Delegates based on these ranges:

• 1 for Delegations between 1-5 athletes and Unified partners
• 2 for Delegations between 6-74 athletes and Unified partners
• 3 for Delegations between 75-149 athletes and Unified partners
• 4 for Delegations between 150-224 athletes and Unified partners
• 5 for Delegations between 225-299 athletes and Unified partners
• 6 for Delegations between 300-374 athletes and Unified partners
• 7 for Delegations between 375 – 450 athletes and Unified partners

I am having success with this formula and it's giving me the correct numbers. However, at the end of my sheet, where I have an overall total, it is not recognizing the cell. In the photo below, I have 12 Athletes + 2 Delegates (this is the column where my formula is located in) + 4 Coaches + 1 Medical staff + 1 AS Staff. This should equal to 20 total in the delegation, but it is only recognizing 18. I've doubled checked all my SUM equations and everything is correct. There's something wrong in my IF equation that is over riding my other formulas. Any advice or help would be appreciated! 🙂

Mark Cronk

Hi @Brittany Pair ,

Try:

=IFERROR(IF([Total ALL Athletes and Unified Partners]@row = 0, 0, IF([Total ALL Athletes and Unified Partners]@row <= 5, 1, IF([Total ALL Athletes and Unified Partners]@row <= 74, 2, IF([Total ALL Athletes and Unified Partners]@row <= 149, 3, IF([Total ALL Athletes and Unified Partners]@row <= 224, 4, IF([Total ALL Athletes and Unified Partners]@row <= 299, 5, IF([Total ALL Athletes and Unified Partners]@row <= 374, 6, IF([Total ALL Athletes and Unified Partners]@row <= 450, 7,"")))))))),"")

Placing number in quotes makes them text. I also tweaked it so there is always an answer no matter what's in your [total all athletes...] column.

Help?

Mark

Mark Cronk

Hi @Brittany Pair ,

Try:

=IFERROR(IF([Total ALL Athletes and Unified Partners]@row = 0, 0, IF([Total ALL Athletes and Unified Partners]@row <= 5, 1, IF([Total ALL Athletes and Unified Partners]@row <= 74, 2, IF([Total ALL Athletes and Unified Partners]@row <= 149, 3, IF([Total ALL Athletes and Unified Partners]@row <= 224, 4, IF([Total ALL Athletes and Unified Partners]@row <= 299, 5, IF([Total ALL Athletes and Unified Partners]@row <= 374, 6, IF([Total ALL Athletes and Unified Partners]@row <= 450, 7,"")))))))),"")

Placing number in quotes makes them text. I also tweaked it so there is always an answer no matter what's in your [total all athletes...] column.

Help?

Mark

Brittany Pair

Mark - you are an absolute life saver!! Thank you so so much!

Mark Cronk

Hi Brittany, Happy to help. Thank you for the vote up and for contributing to the Community.

Mark