Solved

Round function in a range

Posted on 2013-12-29
6
213 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
With the advent of Windows 10, Microsoft is pushing a Get Windows 10 icon into the notification area (system tray) of qualifying computers. There are many reasons for wanting to remove this icon. This two-part Experts Exchange video Micro Tutorial s…

770 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