Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Round function in a range

Posted on 2013-12-29
Medium Priority
219 Views
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?

0
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
• 3
• 3

LVL 11

Expert Comment

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

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

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

Author Comment

ID: 39757074

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

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

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

## Featured Post

Question has a verified solution.

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

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.