What does TEXT formula, format as "?" do?

Question

What does formatting text as "?" actually do? All I know is that it will give a blank cell appearance when the evaluated value is 0.

=TEXT(IF(condition,1,0),"?")

Answer 1

Aside from it's use for fractions, it is used to leave a space for non-significant zero's both to the left and right of the decimal point. This is useful if you are using a fixed font and want to have the decimals aligned but not show the 0's, as you would with a format of 0000.0000

For example, with the cells below formatted as ????.???? and Courier New for the font, see how the decimals are aligned, but leading and trailing zero's are not showing.

Answer 2

A very interesting question. I did some testing and have verified what it represents so I can answer the what does it do however I can't answer for sure the why does it exist part.

Take a look at my sample data:

Both rows B and C seem to match which shows that:

=TEXT(value,"?") is equivalent to =(IF(ROUND(value,0)=0," ",ROUND(value,0))

Keep in mind, although it may be obvious, that TEXT() will always convert the data to text, and no longer be numeric.

My guess as to why it exists is for the =TEXT(value,"?/?") formatting option. In this option you want to display a fraction only if the result is not blank. If either of the question marks are blank then it returns a space.

Answer 3

For why, consider what the "?" is meant to do. It is meant to do precisely as Ron Rosenfeld says:

1) Allow for a character space to be maintained regardless of the output. So the most common use is in "creating a decimal tab" analogous to the "real thing" in Word so all your entries will line up on their decimals and display however many decimal places you wish.

2) Since a common desire in doing 1)'s most common use is to not have zeros padding out the displayed value. (Otherwise you'd use, for the example show, ".0000" for the decimal point and material to the right of it (and whatever for the non-decimal digits).) So the secondary function is to suppress any trailing zeros.

If done to the right of the decimal point the result is exactly as Ron Rosenfeld shows. But what if you put one to the left of the decimal point? Depends on the details of that, and one interesting case, instructive here, is "?" with an input to TEXT() of, say, 12.95:

=TEXT( 12.95, "?" )

which results in "13", no more, no less. It cannot show anything to the right of the decimal point as there is nothing in the format concerning the decimal point or anything to its right. So TEXT() has to round the result to what is rounded at 0 decimal places, exactly as Eric F shows.

So now consider doing that AND having a value that will round to 0:

=TEXT( 0.49, "?" )

What happens is that TEXT() has to round it to 1 place left of the decimal: nothing to the right of it and that is 0. The format suppresses insignificant (trailing at the end of the result) zeros. Since this string of ("0") "has a zero at its end" (and beginning and middle, one can get pretty metaphysical here) that zero is suppressed. As it is the ONLY thing present and its display is suppressed, you are left with "". Just a blank.

By the way, I say "displays" a couple times, but it doesn't just display it as such, it makes it exactly that. So the "13" from above is really 13, not 12.95 in disguise.