How do I stop Excel from adding double quotes to my formula?

this works:

{=MEDIAN((Table1[MonthFinish]=201012)*(Table1[Days]))}

but if I put 201012 into cell A3, this doesn't done work:

{=MEDIAN((Table1[MonthFinish]=A3)*(Table1[Days]))}

when i do Evaluate Formula on the 2nd one...I see that there are double quotes about the 201012 that was pulled from A3...like so:

{=MEDIAN((Table1[MonthFinish]="201012")*(Table1[Days]))}

and as such, all the 201012s pulled from the MonthFinish row come back as FALSE when compared to "201012" (ie, 201012="201012" ) where as they come back as TRUE when I hard code 201012 as it shows up as 201012=201012.

How do I get Excel to not put those quotes around the number?

Alex

Posted 2010-12-29T13:49:36.513

Reputation: 464

1Try {=MEDIAN((Table1[MonthFinish]=VALUE(A3))*(Table1[Days]))}. I have not tested it. – wilson – 2010-12-30T02:54:35.937

@wilson, Yup, that's the ticket. Apparently, the "format" of the cell doesn't impact the actual contents. If the cell was down as a text field and then you add a number...it's stuck as a text even if you change the formating to number. – Alex – 2010-12-30T10:16:13.500

@wilson, please enter that as an answer. Thanks! – Firefeather – 2010-12-30T16:48:26.700

Answers

{=MEDIAN((Table1[MonthFinish]=VALUE(A3))*(Table1[Days]))}

The function =VALUE(text) formats the text as numeric value.

wilson

Posted 2010-12-29T13:49:36.513

Reputation: 4 113

Have you ensured that the cell is formatted as a number?

Right Click on cell>Format Cells>Number

If you have it formatted as a number already, try editing in the cell and hitting enter. Sometimes this will make Excel properly apply the formatting you set.

If neither of those work, try entering in cell A4:

=A3*1

and then point your formulas to that cell and see if it works. If so just enter 201012 into A4 instead of the formula and you should be set.

Hope that helps.

Sux2Lose

Posted 2010-12-29T13:49:36.513

Reputation: 2 962