Formula need to Sum OR Average based on another cell
I need some help with a formula. I have:
=IF([Goal Calculation]@row = "sum", SUM([Quarter 1]@row:[Quarter 4]@row,IF([Goal Calculation]@row = "average", avg([Quarter 1]@row:[Quarter 4]@row))))
The problem is it is only updating to reflect SUM. If I change it to AVERAGE, the cell goes blank. So I switched it and put AVERAGE first and that worked, but then SUM went blank. So it's only reading the first part of the formula.
Any advice?
Best Answer

Try your formula with the modification. The IF statements needed to be separated with more the the comma, I believe its because the SUM function needed to be closed before starting the next.
=IF([Goal Calculation]@row = "sum", SUM([Quarter 1]@row:[Quarter 4]@row), IF([Goal Calculation]@row = "average", AVG([Quarter 1]@row:[Quarter 4]@row)))
Answers

Try your formula with the modification. The IF statements needed to be separated with more the the comma, I believe its because the SUM function needed to be closed before starting the next.
=IF([Goal Calculation]@row = "sum", SUM([Quarter 1]@row:[Quarter 4]@row), IF([Goal Calculation]@row = "average", AVG([Quarter 1]@row:[Quarter 4]@row)))

Perfect! Thank you SO much!
Help Article Resources
Categories
Check out the Formula Handbook template!