Rounding values in tables

[Doc60]

I am trying to round values in a table that have been calculated. I would
like to have values that are >10 rounded to just a whole number and those <10
round to one decimal place. For example 284.8 I would like to have rounded to
285 and 0.25 I would like to have rounded to 0.3. Could you please assist me
with this.

Thank you

 

[Bajbaj]

I am trying to round values in a table that have been calculated. I would
like to have values that are >10 rounded to just a whole number and those <10
round to one decimal place. For example 284.8 I would like to have rounded to
285 and 0.25 I would like to have rounded to 0.3. Could you please assist me
with this.

Thank you

Hi,

Suppose your raw results are in column A, then you make in column B,
CellB1 the formula "=IF(A1<10;ROUND(A1;1);ROUND(A1;0))"
ofcourse you have to draw this formula down for the range you need the
conversion.

In human language the formula means:

if result in A is smaller then 10, then it must be rounded to one
decimal, otherwise it's rounded to one unit.

Greetings Baj.

 

[Doc60]

Bajbaj,

I have recorded the Macro with what you have suggested and I have received a
!Syntax Error, ;. What else would you suggest. Also how can I do this so that
I don't have to use extra cells that already have values in them such as
CellB1.

Thank you.

 

[Doug Robbins - Word MVP]

You need a field construction such as

{ IF { =A1*B1 } > 10 { = A1*B1 \# #,###" } { = A1*B1 \# "##.0" } }

You must use Ctrl+F9 to insert each pair of field delimiters { } and you use
Alt+F9 to toggle off their display.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[Doc60]

Unfortunately I am still getting a Syntax error.

You need a field construction such as

{ IF { =A1*B1 } > 10 { = A1*B1 \# #,###" } { = A1*B1 \# "##.0" } }

You must use Ctrl+F9 to insert each pair of field delimiters { } and you use
Alt+F9 to toggle off their display.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[Doug Robbins - Word MVP]

Do you have a space either side of the operator >?

Copy the field construction that you have character by character (spaces
included) into a message that you post back here so that we can see what it
is.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[Doc60]

Hi Doug,

The following is what I have as the field construction I have put this in by
copy and paste function. I also tried this with a adding a quotation mark
before ",###" but it didn't work. I thought there was a quotation mark
missing.

{IF { =A1*B1 } > 10 { = A1*B1 \# #,###" } { = A1*B1 \# "##.0" } }

Thanks for continuing to assist me with this.

 

[Doug Robbins - Word MVP]

Sorry, there should be a quote mark before the #,### that I missed when
typing my response. If that is missing, you will get a syntax error
message. The correct field construction is:

{IF { =A1*B1 } > 10 { = A1*B1 \# "#,###" } { = A1*B1 \# "##.0" } }

(Note it does appear, at least in this case, that the quote marks are
optional, but if you have one (opening or closing) then you must have the
other one as well so that they are in pairs. The following should also
work:

{IF { =A1*B1 } > 10 { = A1*B1 \# #,### } { = A1*B1 \# ##.0 } }

I am not sure however that this optionality applies to all field switches
and for that reason, it is probably better to adopt the practice of always
using the quotes.)

I assume that you have this entered in the cell at the intersection of the
first row and the third column of a table so that the A1 and B1 are
referring to numbers entered into the first two cells of that row.

and ALL of the field delimiters { } must be insert by using Ctrl+F9

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[Doc60]

Hi Doug,

I did try putting the quote marks in however I still got a Syntax error.
2 questions -

1- I am putting this in the C1 row should I be putting it in another row.
2- When I try to write this into the Macro directly I am trying to use the
Ctrl+F9 to put the "{ }" in but nothing happens. I have also tried to put it
in as a formula into the table and when I tried to put in the "{ }" nothing
happens. So I have to put the "{ }" in manually could that be the problem?

Again thanks for continuing to assist me with this.

 

[Graham Mayor]

Each pair of field brackets should be entered with CTRL+F9 i.e. 4 pairs! So
you should have

{ IF { =A1*B1 } > 10 { = A1*B1 \# ",0" } { = A1*B1 \# ",0.0" } }

(which is a simpler way of expressing your original field construction.)

What do you mean by 'nothng happens'? Nothing should happen unless you
update the field (F9). This field will always have to be updated to produce
the result.

Although not strictly necessary with fields as results I prefer to insert
the results in quotes also so

{ IF { =A1*B1 } > 10 "{ = A1*B1 \# ",0" }" "{ = A1*B1 \# ",0.0" }" }

Would be my choice. This construction can go in any cell you like -
including C1.
It will not work outside the table.


--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[Doug Robbins - Word MVP]

Does your keyboard have an FLock key that must be pressed to enable the
Function Keys?

The field construction should be entered directly into the table, but you
must use Ctrl+F9, not the { and } keys on the keyboard to insert the field
delimiters.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[Tony Jollans]

Your syntax error is because you are using semicolons instead of commas - I
guess bajbaj is in some (non-UK) European location and has his (or her)
separator set to semicolon and you are in the UK or US and have yours set to
comma.