...or does Excel 2007 blow goats when using formulas to evaluate the results of other formulas?

I'm trying to edit my timesheet in Excel to give me a numerical value in one cell whenever another cell contains text.

So, I do the =ISTEXT(E8) formula in F8, and just copy that formula down the column to include the cells in the other column. This gives me FALSE if there is no text in a corresponding cell in the E column, and TRUE if there is text.

Ok, so now I need a numerical value. I do =IF(F6="TRUE","1", "0") in G6, copy that down the column. But, for some reason, excel is not seeing the result of the formula in F6, it is only seeing the formula. So I get a 0 even if it's TRUE in the cell.


Does anyone else have problems with this, and has anyone else worked around it?

i have been upgraded to excel 2010 and i absolutely hate it

I'm not going to pass judgement on either you or excel 2007, even if it is a piece of fluff.

I haven't used excel 2007 and without testing I can't be sure but I'm thinking the error is because you have " around true... I think your forumla should be =if(f6=true, 1, 0)

By having " around your results, 1 and 0, you're kinda making those results text rather than values though excel "should" realise they are values and still be able to add them up.

Do you really need 2 columns to show the result? Change the forumla you have in f8 to be
=if(istext(e8), 1, 0)

Just tested this. Remove the quotation marks around true. I think because true/false is an operator, the excel sheet evaluates it as such, and does not consider it text (which is what you are telling it, when you add the quotations). Because the cell does not contain text, you will always get a return of false (or 0 in your case) for your if else formula.

Ahh, ok. So quotes make things text instead of values. And I'd tried to combine formulas, but I was missing the syntax. Thanks all.

yeah, from a coding perspective, excel data is cast by inference. if there are quotes around something, excel assumes you want it to be a string, even if it only holds numbers. not a terrible way of doing things if you expect it ;)