Solved

Round function in a range

Posted on 2013-12-29
6
210 Views
Last Modified: 2014-01-06
Hi All,

Here is what I have so far code wise:

"=Round((constants!$M$2)*(ConC!G" & r & "),6)"

The left hand side of the equation is fine and so is the sub, end sub and the variables.

What the question is:   Do I have this set up properly to for the range if I am looking to round 6 significant digits?

thanks for your help.
0
Comment
Question by:BostonBob
  • 3
  • 3
6 Comments
 
LVL 10

Expert Comment

by:JEaston
ID: 39745151
I always thought Round only dealt with decimal places, not significant figures.  For example:

1234.56789 to 3 significant figures is 1230, but round to 3 digits I would expect to return 1234.568.

If you need to round to significant figures you would need to use:  ROUND(A1,3-LEN(INT(A1)))

Assuming your data was in cell A1.  Change the 3 (at 3-Len...) to whatever number you wish to use.
0
 

Author Comment

by:BostonBob
ID: 39745292
Sorry, I meant decimal places.  The more I stare at this screen and try to work out my program the more brain cells I lose....:)

Do i have it set up properly?

thanks,
0
 
LVL 10

Expert Comment

by:JEaston
ID: 39745853
I obviously can't see the whole Sub, but I assume this is either part of a loop, or 'r' is passed to the sub.

Therefore your formula would do the following:

Take the value of constants!$M$2 and times it by the value of conC!G[r] ([r] would be replaced with a row number I assume).

This answer would then be rounded to 6 decimal places.  E.g. if the end answer was 12.3456789 it would round to 12.345679.

Obviously the proof is in the testing.  So I would copy your workbook and test a number of likely values and see if the result is what you would expect.
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:BostonBob
ID: 39757074
Sorry for the late reply.

Here is the formula and it seems to work right now:

"=Round((constants!$M$2)*(ConC!G" & r & "),6)"

Now the last item where the "6" is is for the decimal places.  I have now found in my data that I am going to have to assign individual numbers for this in the row because the data itself is not always in 6 decimal places.  Sometimes 2, sometimes, 9, other times far larger.  

So I am going to have to do a one time hard code where I will have the range and assign the integers for each particular row down that range.

The solution that I have been using for other ranges are, as above, "ConC!G" & r &"....but how would I place this kind of notation in a place where the formula is expecting one number?

Does that make sense? thanks!
0
 
LVL 10

Accepted Solution

by:
JEaston earned 500 total points
ID: 39758843
It is possible to replace the 6 with a cell reference.  Assuming your above forumula works, then the following should do what you need (if I understand you correctly):

"=Round((constants!$M$2)*(ConC!G" & r & "),ConC!H" & r & ")"

Assuming you store the number of digits you want to round to in Column H - if not change the 'H' to whichever column you choose to use.
0
 

Author Comment

by:BostonBob
ID: 39759053
K. I will run with that.  thanks!
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
My experience with Windows 10 over a one year period and suggestions for smooth operation
This video shows where to find the word count, how to display it, and what it breaks down to in Microsoft Word.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now