Precision as displayed for certain cells only

[Confused]

Is this possible? Precision as displayed is exactly what I need, but only in
certain cells, not the entire sheet like you get when you select the option
under the calculations tab of the options dialog.

I have a bunch of formulae that work on various numbers that result in a
figure that is formatted to currency. Of course, these are rounded to two
decimal places. But, any further calculations on that number are done on the
whole decimal, not the rounded currency value. For example, I have a cell
whose final output in currency is $3.21, and when I multiply that by ten, I
get $320.88 since that is the underlying whole number. For that particular
cell, that's not what I want, I want it to result in $321.00. Is there a way
to tell just that formula to use the displayed value instead of the
underlying whole number?

I bet it's something simple I just can't find again, thanks in advance for
any help.

Conf.

 

[Niek Otten]

Use the ROUND() function in the second formula, like

=10*ROUND(A1,2)

 

[Gord Dibben]

I think you have a typo.

Did you mean 10 * cellref or 100 * cellref?

Whatever the case, see Niek's ROUND suggestion but you might want

To add the ROUND function to all cells at once you could use a macro.

Sub RoundAdd()
Dim myStr As String
Dim cel As Range
For Each cel In Selection
If cel.HasFormula = True Then
If Not cel.Formula Like "=ROUND(*" Then
myStr = Right(cel.Formula, Len(cel.Formula) - 1)
cel.Value = "=ROUND(" & myStr & ",2)"
End If
End If
Next
End Sub


Gord Dibben MS Excel MVP

 

[Confused]

Ah, yes, I had a typo there, I meant to say 100 * cellref instead of ten,
too much staring at grids of numbers today.

Anyway, the round function works exactly the way I want, see, I knew it
would be simple. I was just looking for the wrong thing in the help file, as
usual.

Thanks again to both,
Conf.