 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Word / Programming / October 2007Rounding values in tables
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 > 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 [quoted text clipped - 3 lines] > > 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. 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. > > 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 [quoted text clipped - 17 lines] > > Greetings Baj. 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.  SignatureEnjoy, Tony > Bajbaj, > [quoted text clipped - 32 lines] >> >> Greetings Baj. 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. SignatureHope 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 >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 [quoted text clipped - 6 lines] > > Thank you Unfortunately I am still getting a Syntax error. > You need a field construction such as > [quoted text clipped - 13 lines] > > > > Thank you 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. SignatureHope 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 > Unfortunately I am still getting a Syntax error. > [quoted text clipped - 20 lines] >> > >> > Thank you 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. > Do you have a space either side of the operator >? > [quoted text clipped - 26 lines] > >> > > >> > Thank you 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 SignatureHope 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 > Hi Doug, > [quoted text clipped - 40 lines] >> >> > >> >> > Thank you 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. > 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 [quoted text clipped - 63 lines] > >> >> > > >> >> > Thank you 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. Signature<>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< ><<> ><<> <>>< ><<> <>>< <>><<> > Hi Doug, > [quoted text clipped - 103 lines] >>>>>>> >>>>>>> Thank you 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. SignatureHope 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 > Hi Doug, > [quoted text clipped - 89 lines] >> >> >> > >> >> >> > Thank you
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.