Yahoo Answers is shutting down on May 4th, 2021 (Eastern Time) and beginning April 20th, 2021 (Eastern Time) the Yahoo Answers website will be in read-only mode. There will be no changes to other Yahoo properties or services, or your Yahoo account. You can find more information about the Yahoo Answers shutdown and how to download your data on this help page.

Trending News

Promoted
Steve VanderBeek
Steve VanderBeek asked in Computers & InternetSoftware · 8 years ago

Excel Calculation Help?

I need to know how I could have the following formula work so that whatever is in A1, when divided by 1000, doesn't include anything below a whole number for use in the rest of the equation.

For example if A1 = 122,900 it would show 122.9 when divided by 1000 but I want it to just be 122 for use in the rest of the formula. Anyone know how to do that?

Here's the formula: =(A1/1000*3+50)/2

Update:

Sorry george that would only work if I created a second cell to do the first part of the calculation and even then it would round to the nearest whole number. I needed it to drop the .9 from 122.9 before moving onto the rest of the calculations and without rounding. Anyways the answer to my problem is TRUNC. final formuala is =((TRUNC(A1/1000,0))*3+50)/2

Trunc does as described and drops decimal points to where you specify as shown below

=TRUNC(Cell,decimal spots to display/use)

=TRUNC(A1/1000,0) displays 122

=TRUNC(A1/1000,1) displays 122.9

=TRUNC(A1/1000,2) displays 122.90

As you noticed I added the dividing option to it but it would do the same to say a number of 1234.9876 and have it display/use 1234. Your option would turn that into 1235.

Update 2:

Grats voyager. you came up with the answer as I updated it. You get best answer!

Update 3:

as soon as it lets me do that anyways...

3 Answers

Relevance
  • C Masters
    Lv 6
    8 years ago
    Favorite Answer

    FLOOR is not incorrect, but I would suggest using INT

    which pretty much has the sole purpose of doing what you describe

    =(INT(A1/1000)*3+50)/2

    hope that helps

  • voyager
    Lv 6
    8 years ago

    a couple of options...

    =(TRUNC(A1/1000)*3+50)/2

    =(ROUNDDOWN(A1/1000,0)*3+50)/2

    =(FLOOR(A1/1000,1)*3+50)/2

  • George
    Lv 5
    8 years ago

    Click on the cell(s) which contains the number you want no decimals on

    Right click and go to format cells

    In category: go to number (second)

    In the decimal places put 0

    This will remove any decimals

Still have questions? Get your answers by asking now.