Forum adverts like this one are shown to any user who is not logged in. Join us by filling out a tiny 3 field form and you will get your own, free, dakka user account which gives a good range of benefits to you: No adverts like this in the forums anymore. Times and dates in your local timezone. Full tracking of what you have read so you can skip to your first unread post, easily see what has changed since you last logged in, and easily see what is new at a glance. Email notifications for threads you want to watch closely. Being a part of the oldest wargaming community on the net. If you are already a member then feel free to login now.

I have a question about Excel. I want it to take a number in one cell, subtract ten, divide by two, and then round the result down to the nearest whole number. I can do all but the rounding part. I am searching the internet, but I am unable to find an article which can explain how to get my function to do what I want. I figure this is a pretty techno-savvy crowd. Can anyone provide any assistance?

Can't you just format the cell to no decimels?

=+(A1-10)/2

[format cell by DECREASE DECIMEL until its whole number only]

I think that works for you.

15=3
20=5

You can format any cell or block of cells to any number of significant figures after the decimal point. It is a setting in the Format menu.

right click on the answer cell. Select Format Cells. Select Number in Category. Change Decimal Places to 0.

That should be it.

Get promoted. Have an associate do it. The associate will have an analyst do it. the analyst will have the intern do it.

Half Billion dollar decisions based on the work of an intern...yea baby yea!

The problem is that if format the cells to no decimals, then it rounds mathematically. (Rounding any decimal under .5 down, and any equal or over .5 up.) I want it to round ALL decimals down to the nearest whole number, regardless of value. (IE, so that if I plug in a value of 13, it will return 1, rather than 1.5 or 2.) Although, I must say I like your last suggestion Frazz. I'll get right on that.

Ooh thats trickier, you got me on that one.

Can I set an if/then function? Like, "If the number ends in .5, subtract .5"?

Ah. What your wanting is truncating. Instead of doing any rounding it just hacks the number off at a certain point. I'm not sure how to do it since I don't work with excel at my job but that may help with your searching.

There's a round function. For instance, =round(A1,2) would take the number in cell A1 and round it to two decimal places.

In your situation you’d want to go with =round(A1,0), replacing A1 with whatever cell the number is in.

The ROUND function rounds mathmatically. INT will round down a real number into an integer.

=INT(cell reference)

I know that I don't excell at its use! Geddit! Sorry...

Here is what I am trying to do. As a hypothetical, I want to put 15 in cell A1 and have it return a value of 2 to cell A2. So, in cell A2, what formula do I put in? I have tried the INT function, entering =INT(A1-10)/2 I have also tried =INT[(A1-10)/2] This did not work either. I tried looking up the Help on the INT function, but it was no help at all.


I want it to subtract ten from the given number and divide by two, rounding down if the division resulted in any number ending in .5.

If it helps, what I am using this for is to design a spreadsheet for druids in D&D, ((3rd edition)) Since my DM plays it that your stats in animal forms increase by the amount of the animal's added stats, (rather than just replacing them) I want to design a spreadsheet that will allow me to plug in a player's physical stats and then return the new physical stats, attacks, and damage for all of said player's various animal forms.

crowdedmind wrote:The ROUND function rounds mathmatically. INT will round down a real number into an integer.

=INT(cell reference)

Ah, I missed that they wanted to round down. Cheers for the pickup, and yeah INT is the way to go.

Jimsolo wrote:Here is what I am trying to do. As a hypothetical, I want to put 15 in cell A1 and have it return a value of 2 to cell A2. So, in cell A2, what formula do I put in? I have tried the INT function, entering =INT(A1-10)/2 I have also tried =INT[(A1-10)/2] This did not work either. I tried looking up the Help on the INT function, but it was no help at all.

I want it to subtract ten from the given number and divide by two, rounding down if the division resulted in any number ending in .5.

Just split the formula over two cells. Assuming the number in question is in cell A1, then in B1 you enter =(A1-10)/2. In C1 you enter =INT(B1). This second cell will get you the result you're after.