Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question
1  
Try {=MEDIAN((Table1[MonthFinish]=VALUE(A3))*(Table1[Days]))}. I have not tested it. –  wilson Dec 30 '10 at 2:54
    
@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 Dec 30 '10 at 10:16
    
@wilson, please enter that as an answer. Thanks! –  Firefeather Dec 30 '10 at 16:48

2 Answers 2

up vote 4 down vote accepted
{=MEDIAN((Table1[MonthFinish]=VALUE(A3))*(Table1[Days]))}

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

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.