Data Validation - Custom Formula

**Brandy** · 10-05-2009, 04:09 PM

Hello,

I would like to use this formula: =round(A1/0.05,0)*0.05 (to round a percentage up or down to the nearest 5%) and be able to use only 1 column - ie. the user will input the percentage into column A and the number will be rounded in column A.

I think this requires using data validation, custom formula, but am not sure as I cannot get it to work.

I know I can put the =round formula in column B and the user inputs the data in column A, but I'd like the input and calculation to occur in the same column.

Any suggestions?

Thank you.

Brandy

**teylyn** · 10-05-2009, 04:17 PM

Hi,

by definition, Excel cells are either for data input, or for calculations. If you want the value of a cell to change within the same cell, you will need to use a VBA solution.

Data validation does not do what you need, since it only checks if the user entered a value that meets a certain condition, but data validation does not manipulate the data.

hth

**Brandy** · 10-05-2009, 04:21 PM

Thank you.

I don't know how to use visual basic.

Is there an easy way to use VBA for that purpose?

Brandy

**oldchippy** · 10-05-2009, 04:24 PM

Hi,

how about a custom format #0.05

**Brandy** · 10-05-2009, 04:27 PM

So, in the Formula field, type #0.05?

When I do, I get an error message, 'The value entered is not valid'.

**oldchippy** · 10-05-2009, 04:32 PM

Hi,

No I was thinking that may be custom the cell A1, right click, Format cells > Custom, but having looked at it again thats probably what you don't want?