IF function returning "TRUE"/"FALSE" literals rather than arguments entered

[Betherski]

I am having trouble with an Excel IF function. If I have a valid child project ID in column K (> 8 characters), then I want to use it to populate my cell. Otherwise, I want to populate my cell with the parent project ID in column J. Here is my IF statement.

=IF(LEN(K2)<8,J2,K2)

The result I am getting is that my cells are populating with the literal "TRUE" (in both true and false circumstances).

One thing I tried just for grins was to change the true/false arguments to literals, rather than cell references, i.e.

=IF(LEN(K2)<8,"APPLE","ORANGE")

Interestingly, this did not fix the formula, but now populated my cells with "FALSE" (in both true and false circumstances).

I don't know what else to try. Any help would be greatly appreciated!

 

[Tom Urtis]

With your second formula I get either apple or orange like I'd expect.

With your first formula, try posting what examples of data you have in columns J and K.

In both cases, your results are different than what they should be unless something else is going on, maybe with custom formatting of the formula-containing cells if J and K contain numbers.

Post a screen shot of your data (you can copy a small range from your worksheet and paste it into this web page).

 

[Betherski]

Here is a bizarre update. My spreadsheet is extremely large (~75,000 rows). I am using Excel 2007, so that should be okay, right? My data in the first few hundred rows is very sketchy, so I was updating the formula in the bottom rows to then copy upward. I went to copy a portion for this post, and rather than copy rows in the 75k range, I went up to the beginning of my "good" data (about row 620) and copied the same formula..... and it worked!!! I'm thinking there was some sort of an Excel bug working with rows that high??? To further confuse things, I then copied the formula from row 620 down to all remaining rows in the spreadsheet, and it still worked fine - even in the very last rows that I have been fighting with all night.

The good news is that my problem now appears to be fixed. The bad news is that I am completely baffled!

Anyway, here is the now working formula, in the first column (followed by the values examined in columns J & K):

<TABLE style="WIDTH: 229pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=304 border=0><COLGROUP><COL style="WIDTH: 116pt; mso-width-source: userset; mso-width-alt: 5632" width=154><COL style="WIDTH: 55pt; mso-width-source: userset; mso-width-alt: 2669" width=73><COL style="WIDTH: 58pt; mso-width-source: userset; mso-width-alt: 2816" width=77><TBODY><TR style="HEIGHT: 51pt; mso-height-source: userset" height=68><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 116pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 51pt; BACKGROUND-COLOR: #a5a5a5" width=154 height=68>Project Tracking ID (Child Project if Present, else Parent)</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 55pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" width=73>Parent Project ID</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 58pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" width=77>Child Project ID</TD></TR><TR style="HEIGHT: 20.1pt; mso-height-source: userset" height=26><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 20.1pt; BACKGROUND-COLOR: transparent" height=26>P0223143</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 55pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=73>P0223143</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 58pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=77></TD></TR><TR style="HEIGHT: 20.1pt; mso-height-source: userset" height=26><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 20.1pt; BACKGROUND-COLOR: transparent" height=26>P0253480</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 55pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=73>P0223143</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 58pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=77>P0253480</TD></TR><TR style="HEIGHT: 20.1pt; mso-height-source: userset" height=26><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 20.1pt; BACKGROUND-COLOR: transparent" height=26>P0254728</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 55pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=73>P0223143</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 58pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=77>P0254728</TD></TR><TR style="HEIGHT: 20.1pt; mso-height-source: userset" height=26><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 20.1pt; BACKGROUND-COLOR: transparent" height=26>P0236138</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 55pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=73>P0223263</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 58pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=77>P0236138</TD></TR><TR style="HEIGHT: 20.1pt; mso-height-source: userset" height=26><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 20.1pt; BACKGROUND-COLOR: transparent" height=26>P0223277</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 55pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=73>P0223277</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 58pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=77></TD></TR><TR style="HEIGHT: 20.1pt; mso-height-source: userset" height=26><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 20.1pt; BACKGROUND-COLOR: transparent" height=26>P0275078</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 55pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=73>P0223341</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 58pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=77>P0275078</TD></TR></TBODY></TABLE>