1. Registered User
Join Date
Apr 2009
Posts
3

I am trying to multiple one cell by a set number and then I need to have the result rounded to the nearest tens. If the number ends with a 4 or less I need it to round to the lower tens. Example 8654, I need to have rounded to 8650. Also, if the number ends in a 5 or above I need it to round to the next highest tens. I have multiple rows to do this with so I was thinking of a if then statement but can't figure it out.

2. Registered User
Join Date
Apr 2009
Posts
6
This is really simple. You dont need to use if else. You can use simple math functions.

Suppose your numbers are in A column, then

=ROUND(A1/10,0)*10

formula will do your job. You can write the formula for one cell and then populate all the rows using autofill. I think you know this part.

The following will be the output of using the above formula.

8454 8450
8455 8460
8456 8460
8457 8460
8458 8460
8459 8460
8460 8460
8461 8460
8462 8460
8463 8460
8464 8460
8465 8470

3. Registered User
Join Date
Apr 2009
Posts
6
ha ha ha. I just found a better formula for this.

=ROUND(A1,-1)

Cheers

4. Registered User
Join Date
Apr 2009
Posts
3
Ok that works except there is a rounding error during the multiplication process. Here's everything that I'm inputting, E3(cell with my number)*1.026,-1. The problem is that on some of the results it is being rounded down instead of up. I have one that I've found so far that when the cell is multiplied by the 1.026 only the result drops the decimals and gives me 37285but when I use the rounding formula in the equation it rounds the result to 37280 instead of 37290.

5. Registered User
Join Date
Apr 2009
Posts
3
I checked my 12,000 plus records and found that half of them when ending in 5 rounded up as I needed but the other half rounded down. The formula is the same in all the cells.

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•