3

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?

3
  • 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
4
{=MEDIAN((Table1[MonthFinish]=VALUE(A3))*(Table1[Days]))}

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

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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