Sum a column where the value in the column is generated from a formula

K Miller

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

Michael Pappas

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:

1. Change the nested if statement to = [Allocation %]@row * 40
2. Put the entire Nested IF statement inside of a VALUE function. => = VALUE(IF(......))

Paul Newcome

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.

Michael Pappas

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:

1. Change the nested if statement to = [Allocation %]@row * 40
2. Put the entire Nested IF statement inside of a VALUE function. => = VALUE(IF(......))

K Miller

That worked. Thank you.

Paul Newcome

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.