Sum a column where the value in the column is generated from a formula
I have a column (Column B) that returns a value based on a nested if statement; column A = x% Column B returns a value based on the % entered in column A. Here is the nested if statement:
= IF([Allocation %]@row = 0.05, "2", IF([Allocation %]@row = 0.1, "4", IF([Allocation %]@row = 0.2, "8", IF([Allocation %]@row = 0.3, "12", IF([Allocation %]@row = 0.4, "16", IF([Allocation %]@row = 0.5, "20", IF([Allocation %]@row = 0.6, "24", IF([Allocation %]@row = 0.7, "28", IF([Allocation %]@row = 0.8, "32", IF([Allocation %]@row = 0.9, "36", IF([Allocation %]@row = 1, "40"))))))))))
I am trying to sum the values in Column B and it just returns 0. Column B is column type: text/number
Best Answers

Right now, it looks like the value that you are producing using the IF statement is being read as text, not as a number.
Two possible solutions:
 Change the nested if statement to = [Allocation %]@row * 40
 Put the entire Nested IF statement inside of a VALUE function. => = VALUE(IF(......))

Whenever you put quotes around a number, you are converting it to a text value. A third option would have been to remove the quotes from around the numbers in the nested IF formula.
Answers

Right now, it looks like the value that you are producing using the IF statement is being read as text, not as a number.
Two possible solutions:
 Change the nested if statement to = [Allocation %]@row * 40
 Put the entire Nested IF statement inside of a VALUE function. => = VALUE(IF(......))

That worked. Thank you.

Whenever you put quotes around a number, you are converting it to a text value. A third option would have been to remove the quotes from around the numbers in the nested IF formula.
Help Article Resources
Categories
Check out the Formula Handbook template!