Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Round function in a range

Posted on 2013-12-29
6
Medium Priority
?
222 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 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

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 2000 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
With its various features, Office 365 can not only help you with your day-to-day business tasks, it can also do wonders for your marketing campaign.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

564 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