Hi Guys

I hope you can now help me. I have a complex formular for the File Name of a document that is made up of 4 parts.

=CONCATENATE(D8,"-",(LOOKUP(E8,lookUp!$A$3:$B$10)),"-",F8,"-",(SUBSTITUTE(G8,".","")))

G8 is the revision of a document and I want it to display without the decimal.

eg, 0.5 - 05
1.1 - 11
2.0 - 20

The above work for all numbers except the whole numbers, ie. 1.0, 2.0. For these numbers the formula only displays 1 or 2.

eg. revision - 0.2
Displays SA99-ADS-PPL-0000202-02

revision - 1.2
Displays SA99-PSP-REP-0000036-12

revision - 1.0
Displays SA99-PSP-PLN-0000413-1

Please HELP!!!!!

Nicole




On Thursday, January 22, 2009 6:44 AM jon wrote:

Hi,
I have a price list that displays all prices to 2 decimal places, but some
prices are actually to 8 or 9.

I have copied all the fields and done a Paste Special Values, which has
removed the calculations, but I now need to change all the fields to
actually be 2 decimal places, not just to display 2 decimal places.

Is there a way of doing this ?

Thanks

Jon


On Thursday, January 22, 2009 7:06 AM Mike wrote:

Jon,


And if a number does not have any decimal part what do you want to display
.00 or something else?

If it is .oo then format as a number with 2 decimal places.

Mike


"jon" wrote:


On Thursday, January 22, 2009 7:30 AM jon wrote:

Hi Mike,
Thanks for your reply

Formatting a field in Excel doesn't actually change the data that it holds,
it just changes how it is displayed.

So for example, if field A1 holds a value 7.569585445 then formatting it as
a number with 2 dp willl display as 7.57 but it will still really be
7.569585445 .

So if that value is the price for one widget, and in cell C1 I multiply it
by 1000 to get the price for 1000 it will show as 7569.59 but it should be
7570.

The products we sell are sold in the tens of thousands, so it can soon show
significant differences in price.

Do you have any further suggestions ?

Jon





"Mike H" wrote in message
...


On Thursday, January 22, 2009 7:45 AM Mike wrote:

Hi,

Yes now I understand the question, try this

=ROUND(A1,2)

7.569585445 now becomes 7.57 and multiplying by 1000 = 7570

Mike



"jon" wrote:


On Thursday, January 22, 2009 8:02 AM jon wrote:

Thanks Mike,
I am aware of the Round function, but how would I apply it to thousands of
fields (without lots of manual work)?

Is there a way of rounding all fields that are selected like you can with
the format painter ?

So far, the only way I can find is to copy the woksheet, then delete all the
prices (so I titles and colour layout etc) then link field A1 to =
ROUND(SHEET1!A1,2) then drag this accross the worksheet.

But this is still time consuming when I have so many thousands of worksheets
to do.

Thanks


Jon


"Mike H" wrote in message
...


On Thursday, January 22, 2009 8:17 AM David Biddulph wrote:

Another option if you have set to display 2 decimal places is to use the
"precision as displayed" option, but be careful that it doesn't upset other
data, and you may want to switch the option back again after you've used it
and saved the new data.
--
David Biddulph

"jon" wrote in message
...


On Thursday, January 22, 2009 8:26 AM JohnMansfiel wrote:

One option would be to use a macro like the one below to mass-update formulas
to include the rounding function:

Sub Add_Rounding()

Dim cellRange As Range
Dim Rng As Range
Dim cellFormula As String

On Error Resume Next

Set cellRange = Range("A1:C10").SpecialCells(xlCellTypeFormulas)

For Each Rng In cellRange

cellFormula = Mid(Rng.Formula, 2, 1024)
If InStr(UCase(cellFormula), UCase("Round")) = 0 Then
Rng.Formula = "=round(" & cellFormula & ",0)"
End If

Next Rng

End Sub


--
John Mansfield
cellmatrix.net


"jon" wrote:


On Thursday, January 22, 2009 9:40 AM jon wrote:

Hi John,
I really like this option. ( and it works like a dream)

Is it possible to paste values as well for the same cells ,so any fields
that are still formula fields get changed to actual values ?


Thanks

Jon

"John Mansfield" wrote in message
...