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

Question

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?

Answer 1

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

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

Answer 2

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.