Solved

Round function in a range

Posted on 2013-12-29
6
216 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 11

Expert Comment

by:John Easton
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 11

Expert Comment

by:John Easton
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
Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 

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 11

Accepted Solution

by:
John Easton 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

Scamming the Scammers!

Have you ever heard of Scam Baiting?
It's a highly entertaining sport that you can participate in.
Introduction to beating scammers at their own game and how you can help
Share your thoughts, ideas and experiences on the topic.
Links to top Anti-Scam resources provided.

Question has a verified solution.

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

Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

739 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