Integer part of a number

users
#1

Hello,

could anyone tell me which formula I should use for the integer part of a
cell to be shown in another cell, and for the value of this cell to be
changed, since, for example, 5,77 would become equal to 5?

When finding out who won in an election, if one group or party gets a
result of 5,28, they get 5 seats, if the result is 0,77, they get no seat
at all, I would then want the figure 5 and the figure 0 to appear in a
cell.

But I need the real value to be 5 and 0, not only to be shown as such,
since these values (0 and not 0,77) will be used in another operation (to
know which group gets the last remaining seat).

I hope my question is not too unclear, thank you for your answers anyway,

G. Siche

#2

= Int()

--Dan

#3

could anyone tell me which formula I should use for the integer part of a
cell to be shown in another cell,

The function you're probably looking for is INT().

Example:
A1: 0.77
B1: =INT(A1) → 0
C1: =A1*2 → 1.54
D1: =B1*2 → 0

and for the value of this cell to be
changed, since, for example, 5,77 would become equal to 5?

I guess I don't understand the restrictions in your spreadsheet. Without knowing any further details of your application, I'd say, why not use the value calculated by INT() instead of the original value in any subsequent operations?

-rl

#4

Hi :slight_smile:
There are other options such as Roundup.  If you want to get the nearest whole number so that .5 or over gets rounded up and less than .5 gets rounded down then one way might be something like

E1: =INT(A1+0.5)    → 1

Hmm, there has got to be a specific function that does rounding correctly. 
Regards from
Tom :slight_smile:

#5

Good morning Gilles & others,

Provided that I got your problem right,

I think the answer is:

SumVotes_group1 = 5,78(in Calc e.g. in cellC3)

SumVotes_group2 = 7,34(in C4)

SumVotes_group3= 0,78(in C5)

SumVotes_ group4= 0,99(in C6)

The answer could be:

a >>write following formula in(e.g.)cellD3" =IF(c3<1;0;INT(c3))"

b >>copy the formula to d4..d6

The formula translated into english:

"if value in cell c3 is /_less than_/ '1' then d3 shall be '0', else (-- if c3 is '1' or bigger then -- ) use the integer of value in c3)"

I hope this is of help.

Pertti Rönnberg

#6

You are overthinking the problem: if C3 is positive, that's no different from
=INT(C3)

Brian Barker

#7

Hi :slight_smile:
The best answer in this thread was the first answer, by Rolf Lochbühler.  Brian is right as usual but Pertti's answer deals with unexpectedly wrong counts.
Regards from
Tom :slight_smile:

#8

It does nothing of the sort, of course.

Brian Barker

#9

Am 15.10.2012 09:49, Pertti Rönnberg wrote:

"if value in cell c3 is /_less than_/ '1' then d3 shall be '0', else (--
if c3 is '1' or bigger then -- ) use the integer of value in c3)"

=MIN(0;INT(C3))

#10

Am 15.10.2012 10:45, Brian Barker wrote:

could anyone tell me which formula I should use for the integer part
of a cell to be shown in another cell, ...

The answer could be:
=IF(C3<1;0;INT(C3))

You are overthinking the problem: if C3 is positive, that's no different
from
=INT(C3)

Brian Barker

Reportedly, negative numbers do exist.

#11

Indeed. Which is why your last answer is also wrong!

#12

This is getting sillier! This formula does not match the definition above - and indeed for non-negative C3 is identically zero! Try
=0
instead.

Brian Barker

#13

But not, it would seem, in the original questioner's election results. Unless you know how to cast a negative vote ...

Brian Barker

#14

Andreas Säger wrote:

Am 15.10.2012 10:45, Brian Barker wrote:

could anyone tell me which formula I should use for the integer part
of a cell to be shown in another cell, ...

The answer could be:
=IF(C3<1;0;INT(C3))

You are overthinking the problem: if C3 is positive, that's no different
from
=INT(C3)

Brian Barker

Reportedly, negative numbers do exist.

ABS(INT(C3))

#15

Am 15.10.2012 16:20, Brian Barker wrote:

Am 15.10.2012 09:49, Pertti Rönnberg wrote:

"if value in cell c3 is /_less than_/ '1' then d3 shall be '0', else
(-- if c3 is '1' or bigger then -- ) use the integer of value in c3)"

=MIN(0;INT(C3))

This is getting sillier! This formula does not match the definition
above - and indeed for non-negative C3 is identically zero! Try
=0
instead.

Brian Barker

Sorry, should be:
=MAX(0;INT(C3))

#16

Now you are assuming (1) that negative election results are possible, and (2) that they should be given equal weight with positive ones. Perhaps you can find me a bank where withdrawals increase my balance just as deposits do?

Brian Barker

#17

Again, for positive C3, that is identical to
=INT(C3)
- which was suggested by someone a long way earlier in this thread!

Brian Barker

#18

Brian Barker wrote:

Now you are assuming (1) that negative election results are possible,

Well, with the way Romney's doing... :wink:

I was just posting in reference to comment about negative numbers and not the original post.

#19

Were there negative voters, you could use =TRUNC(C3).
This has been an interesting discussion because I have learnt that INT(-1.1) is 2 and if I want the integer part of -1.1 I need to use TRUNC.
INT returns the next integer less than or equal to the value. TRUNC returns the integer part of the value (in the format I have shown).
steve

#20

Hi everybody,
When I tried to give an answer to Gilles' basic question I proposed "my" formula based on the reality that I have never experienced negative voting.
The very interesting (theoretical) discussion this caused shows that there may be several correct solutions.

I can imagine that Gilles (and me too) is curious to know what is the best answer to his problem.
Even if I did (I admit) some "overthinking" I cannot see that my answer is wrong.

So, what is the best way for Gilles to continue?
Pertti Rönnberg