Format cell to show as if rounding to 0.5

[Mitnageek]

Hi

I want to format a cell to show value to the nearest half number in decimal.

I do not want to use the ROUND formula or VBA because this is to be distributed for manual entry to non-proficient users (non-sentient in some cases!). Equally, I cannot rely on macros being enabled, or permitted. Finally, multiple columns are not viable in this scenario.

I could use fractions formats, to nearest 1/2, but it looks ugly. If no-one can offer better, that's what I'll end up with.

Any ideas very gratefully received.

(if anyone can show me an alternative using round and multi columns, I'll take it!)

 

[Scott Huish]

This is not possible without using VBA or an extra column.

Why not just use the extra column and protect it so that the user can't change this column?

Excel Workbook
	A	B
1	0.7	0.5
2	0.8	1
3	0.5	0.5
4	0.55	0.5
Sheet1

 

[Mitnageek]

This is not possible without using VBA or an extra column.

Why not just use the extra column and protect it so that the user can't change this column?

Thanks HotPepper, I know I could do that but...

This spreadsheet is going to printed out and filled in by pencil, then keyed in.
Additional columns will look ugly and add to visual confusion.
If I wanted to do that, I would not have needed to ask this question.

This is all about visual presentation to (I can't stress this enough) inexperienced users of Excel. The form has to guide and assist. There is already a requirement for 8 data-entry columns on the sheet - 4 each of number and text, in pairs - along with 7 columns of supporting/index information. I really can't afford to add the four further columns that using ROUND would require.

The formatting is to remind the informants that they have been asked to give values accurate to the nearest half.

Ultimately, I can do the rounding when I process the info anyway, so this is all about visual presentation.

I there no way to trick excel into showing the format-rounded fraction as a decimal? I know this 'makes no sense' but that doesn't always stop something from being do-able..

 

[Mitnageek]

To clarify the slightly misleading last line of my initial post:

I've previously used "centre across" and column widths of 1 pixel with sheet protection to give users the impression they are over-typing a formula generated value, when in reality they're just entering data in the next column along. (Thanks very much to the experts on this site for that trick).

I'm wondering if there's some similar trickery that could work in the opposite direction..?

(Apologies to HotPepper - I can see how that last line would lead to the response you gave. Thanks again)

 

[Scott Huish]

Sorry, there is no trick. You either need to use VBA to round the value in place, or you need to use a formula with a second column as I posted. Excel does not have the capability to do formulaic number formatting.

 

[shg]

How about data validation?

Allow: Custom, Formula: =A1=ROUND(A1*2, 0)/2 Error message: Input values to the nearest 0.5, e.g., 2.5, 3.0, ...

 

[Mitnageek]

Thanks both. Nice validation formula. I will stick with the fraction format and just live with thinking it's ugly. I'm sure I'll get over it.

 

[llangham]

The data validation is what I needed. Thanks!