Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2009
    Posts
    3

    Unanswered: multiple math functions

    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. #2
    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. #3
    Join Date
    Apr 2009
    Posts
    6
    ha ha ha. I just found a better formula for this.

    =ROUND(A1,-1)

    where A1 has your number.

    Cheers

  4. #4
    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. #5
    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
  •