calculated columns : comparing a column with a text value.

**kryptonite2013** · 12-05-2013, 07:55 PM

Guys,

I am using pivot tables trying to create a calculated column on "column1" which is text column in the excel sheet and below is the logic:
if column1 is equal to "Tier1" then return 1 or return 0

so i write this as

IF(Column1 = "Tier1",1,0)

for the life of me this always return a 0 and never a 1, even though the value of Column1 is Tier1 with no spaces and same case.

does any one know what i might be doing wrong here.

thanks

**David A Coop** · 12-05-2013, 08:05 PM

Hi Kryptonite,

Welcome to the forum! If this is outside the Pivot Table, "Column1" is not a valid reference in an IF statement, you need to refer to a cell.

Try =IF(A1="Tier1",1,0) where you are looking at the value in A1.

**FDibbins** · 12-05-2013, 08:19 PM

Hi and welcome to the forum

As david pointed out, column1 is not a cell ref (unless it is a named range?).

**David A Coop** · 12-05-2013, 08:35 PM

Originally Posted by David A Coop

Hi Kryptonite,

Welcome to the forum! If this is outside the Pivot Table, "Column1" is not a valid reference in an IF statement, you need to refer to a cell.

Try =IF(A1="Tier1",1,0) where you are looking at the value in A1.

Hello again, it occurred to me that you are trying to calculate the formula WITHIN the Pivot Table. I don't know how to do this, I think it would be better to have a helper column outside the PT, and include that column in the table. You could pick up the number with =VALUE(RIGHT(A2,1)) if you have less than 10 Teirs.

How many Tiers are there?