Rounding numbers on unbound totals box

[Guest]

I have an Unbound totals box on a Form Footer that gets the diference in
value between two other Totals boxes. But under certain conditions the Value
displays as for example, 1.4. How can I round this box value up when the
result is a fraction?

The default value in the box is:

=[High]/[Low]

 

[Allen Browne]

Try:
= - Int( - [High] / [Low])

 

[Guest]

Difference? But you're dividing! Otherwise Allen's trick still works.

- Int(-([High]-[Low]))

Rod

 

[Guest]

Thanks Alan, it worked to a point, but in the downwards direction, do you
know how to round up?. I know the score is 1.4, but hopefully things will be
clearer below as to why)


Thanks Rod, yes, you're right of course. more a case of bad english as well
as bad maths on my part, what I meant was percentage difference.

the reason for wanting this is set out below in a message that I posted
under another title which relates to the Conditional Formatting Feature
at the moment, my boxes are

Low = 2 Med = 4 High = 6.

I want the colours to reflect that. I couldn't get the dialogue box to work
out the sums, so set up 3 boxes to give me the Dmax, Dmin for the column,
then used the two figures to arrive at a median for the mid value. Then tell
the Condititional Format If Higher than or equal to [High], make one colour,
If Lower or Equal to [Low], make the second colour, and if between [High] and
[Low] make the 3rd colour for the mid value.


In my Form/table, i have a list of numbers representing the amount of times
a

user has got a question wrong, based on the field [x_wrong].

for example:

[Qstn_ID] [Xrwong] [Question] Colour

4 15 blah, blah Green
6 9 " Green
18 27 " Red
4 43 " Red
9 4 " Blue
2 8 " Green
33 3 " Blue
9 3 " Blue
84 22 “ Red

I want to be able to colour code the 3 ranges available, based on the
highest 3rd, middle third, and lowest 3rd percentages of the values using the
fluctuating highest value, and lowest value as the index. Because the values
are constantly changing, i cannot use a fixed figure; say >33 because the
highest value may never reach 33.

Difference? But you're dividing! Otherwise Allen's trick still works.

- Int(-([High]-[Low]))

Rod

I have an Unbound totals box on a Form Footer that gets the diference in
value between two other Totals boxes. But under certain conditions the Value
displays as for example, 1.4. How can I round this box value up when the
result is a fraction?

The default value in the box is:

=[High]/[Low]

 

[Allen Browne]

I'm not clear what you mean by "round up."

Did you just mean "round", i.e. the 1.4 should round to the nearest integer
(so down to 1)?

Or did you mean "round up" where 1.1 should round up to 2?

The answer I gave was for "round up".
If you just wanted "round", it's even easier:
=Round([High]/[Low], 0)

Thanks Alan, it worked to a point, but in the downwards direction, do you
know how to round up?. I know the score is 1.4, but hopefully things will
be
clearer below as to why)


Thanks Rod, yes, you're right of course. more a case of bad english as
well
as bad maths on my part, what I meant was percentage difference.

the reason for wanting this is set out below in a message that I posted
under another title which relates to the Conditional Formatting Feature
at the moment, my boxes are

Low = 2 Med = 4 High = 6.

I want the colours to reflect that. I couldn't get the dialogue box to
work
out the sums, so set up 3 boxes to give me the Dmax, Dmin for the column,
then used the two figures to arrive at a median for the mid value. Then
tell
the Condititional Format If Higher than or equal to [High], make one
colour,
If Lower or Equal to [Low], make the second colour, and if between [High]
and
[Low] make the 3rd colour for the mid value.


In my Form/table, i have a list of numbers representing the amount of
times
a

user has got a question wrong, based on the field [x_wrong].

for example:

[Qstn_ID] [Xrwong] [Question] Colour

4 15 blah, blah Green
6 9 " Green
18 27 " Red
4 43 " Red
9 4 " Blue
2 8 " Green
33 3 " Blue
9 3 " Blue
84 22 “ Red

I want to be able to colour code the 3 ranges available, based on the
highest 3rd, middle third, and lowest 3rd percentages of the values using
the
fluctuating highest value, and lowest value as the index. Because the
values
are constantly changing, i cannot use a fixed figure; say >33 because the
highest value may never reach 33.

Difference? But you're dividing! Otherwise Allen's trick still works.

- Int(-([High]-[Low]))

Rod

I have an Unbound totals box on a Form Footer that gets the diference
in
value between two other Totals boxes. But under certain conditions the
Value
displays as for example, 1.4. How can I round this box value up when
the
result is a fraction?

The default value in the box is:

=[High]/[Low]

 

[Guest]

Hello Allen,

I meant the latter, "round up" where 1.1 should round up to 2

For some reason my 1.4 box rounded 'down' to one, instead of up... but then
I have been having a crazy time with this database and 'memory issues' on a
machine with 2,513,856MB of 'available memory'... I need to check some other
things that may be related, meanwhile I have resolved the conditional
formatting issue below:

thanks for your help all the same, it is always appreciated.


.... If you're in the mood for getting your head round a very tricky query.
You're more than welcome to have a go at this one... it's driving me to
despair!

http://www.microsoft.com/office/com...8ada&catlist=&dglist=&ptlist=&exp=&sloc=en-us

I'm not clear what you mean by "round up."

Did you just mean "round", i.e. the 1.4 should round to the nearest integer
(so down to 1)?

Or did you mean "round up" where 1.1 should round up to 2?

The answer I gave was for "round up".
If you just wanted "round", it's even easier:
=Round([High]/[Low], 0)

Thanks Alan, it worked to a point, but in the downwards direction, do you
know how to round up?. I know the score is 1.4, but hopefully things will
be
clearer below as to why)


Thanks Rod, yes, you're right of course. more a case of bad english as
well
as bad maths on my part, what I meant was percentage difference.

the reason for wanting this is set out below in a message that I posted
under another title which relates to the Conditional Formatting Feature
at the moment, my boxes are

Low = 2 Med = 4 High = 6.

I want the colours to reflect that. I couldn't get the dialogue box to
work
out the sums, so set up 3 boxes to give me the Dmax, Dmin for the column,
then used the two figures to arrive at a median for the mid value. Then
tell
the Condititional Format If Higher than or equal to [High], make one
colour,
If Lower or Equal to [Low], make the second colour, and if between [High]
and
[Low] make the 3rd colour for the mid value.


In my Form/table, i have a list of numbers representing the amount of
times
a

user has got a question wrong, based on the field [x_wrong].

for example:

[Qstn_ID] [Xrwong] [Question] Colour

4 15 blah, blah Green
6 9 " Green
18 27 " Red
4 43 " Red
9 4 " Blue
2 8 " Green
33 3 " Blue
9 3 " Blue
84 22 “ Red

I want to be able to colour code the 3 ranges available, based on the
highest 3rd, middle third, and lowest 3rd percentages of the values using
the
fluctuating highest value, and lowest value as the index. Because the
values
are constantly changing, i cannot use a fixed figure; say >33 because the
highest value may never reach 33.

Difference? But you're dividing! Otherwise Allen's trick still works.

- Int(-([High]-[Low]))

Rod

:

I have an Unbound totals box on a Form Footer that gets the diference
in
value between two other Totals boxes. But under certain conditions the
Value
displays as for example, 1.4. How can I round this box value up when
the
result is a fraction?

The default value in the box is:

=[High]/[Low]

 

[Allen Browne]

Are you certain you included the minus signs?

If so, there must be some point at which Access is misunderstanding your
data types, so that you need to typecast:
http://allenbrowne.com/ser-45.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hello Allen,

I meant the latter, "round up" where 1.1 should round up to 2

For some reason my 1.4 box rounded 'down' to one, instead of up... but
then
I have been having a crazy time with this database and 'memory issues' on
a
machine with 2,513,856MB of 'available memory'... I need to check some
other
things that may be related, meanwhile I have resolved the conditional
formatting issue below:

thanks for your help all the same, it is always appreciated.


... If you're in the mood for getting your head round a very tricky query.
You're more than welcome to have a go at this one... it's driving me to
despair!

http://www.microsoft.com/office/com...8ada&catlist=&dglist=&ptlist=&exp=&sloc=en-us

I'm not clear what you mean by "round up."

Did you just mean "round", i.e. the 1.4 should round to the nearest
integer
(so down to 1)?

Or did you mean "round up" where 1.1 should round up to 2?

The answer I gave was for "round up".
If you just wanted "round", it's even easier:
=Round([High]/[Low], 0)

Thanks Alan, it worked to a point, but in the downwards direction, do
you
know how to round up?. I know the score is 1.4, but hopefully things
will
be
clearer below as to why)


Thanks Rod, yes, you're right of course. more a case of bad english as
well
as bad maths on my part, what I meant was percentage difference.

the reason for wanting this is set out below in a message that I posted
under another title which relates to the Conditional Formatting Feature
at the moment, my boxes are

Low = 2 Med = 4 High = 6.

I want the colours to reflect that. I couldn't get the dialogue box to
work
out the sums, so set up 3 boxes to give me the Dmax, Dmin for the
column,
then used the two figures to arrive at a median for the mid value. Then
tell
the Condititional Format If Higher than or equal to [High], make one
colour,
If Lower or Equal to [Low], make the second colour, and if between
[High]
and
[Low] make the 3rd colour for the mid value.


In my Form/table, i have a list of numbers representing the amount of
times
a
user has got a question wrong, based on the field [x_wrong].

for example:

[Qstn_ID] [Xrwong] [Question] Colour

4 15 blah, blah Green
6 9 " Green
18 27 " Red
4 43 " Red
9 4 " Blue
2 8 " Green
33 3 " Blue
9 3 " Blue
84 22 “ Red

I want to be able to colour code the 3 ranges available, based on the
highest 3rd, middle third, and lowest 3rd percentages of the values
using
the
fluctuating highest value, and lowest value as the index. Because the
values
are constantly changing, i cannot use a fixed figure; say >33 because
the
highest value may never reach 33.

:

Difference? But you're dividing! Otherwise Allen's trick still works.

- Int(-([High]-[Low]))

Rod

:

I have an Unbound totals box on a Form Footer that gets the
diference
in
value between two other Totals boxes. But under certain conditions
the
Value
displays as for example, 1.4. How can I round this box value up when
the
result is a fraction?

The default value in the box is:

=[High]/[Low]

 

[Guest]

Allen,

To be honest, I most likely did omit the minus sign, suffice to say that the
problem has gone now away.

Thanks.

Are you certain you included the minus signs?

If so, there must be some point at which Access is misunderstanding your
data types, so that you need to typecast:
http://allenbrowne.com/ser-45.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hello Allen,

I meant the latter, "round up" where 1.1 should round up to 2

For some reason my 1.4 box rounded 'down' to one, instead of up... but
then
I have been having a crazy time with this database and 'memory issues' on
a
machine with 2,513,856MB of 'available memory'... I need to check some
other
things that may be related, meanwhile I have resolved the conditional
formatting issue below:

thanks for your help all the same, it is always appreciated.


... If you're in the mood for getting your head round a very tricky query.
You're more than welcome to have a go at this one... it's driving me to
despair!

http://www.microsoft.com/office/com...8ada&catlist=&dglist=&ptlist=&exp=&sloc=en-us

I'm not clear what you mean by "round up."

Did you just mean "round", i.e. the 1.4 should round to the nearest
integer
(so down to 1)?

Or did you mean "round up" where 1.1 should round up to 2?

The answer I gave was for "round up".
If you just wanted "round", it's even easier:
=Round([High]/[Low], 0)


Thanks Alan, it worked to a point, but in the downwards direction, do
you
know how to round up?. I know the score is 1.4, but hopefully things
will
be
clearer below as to why)


Thanks Rod, yes, you're right of course. more a case of bad english as
well
as bad maths on my part, what I meant was percentage difference.

the reason for wanting this is set out below in a message that I posted
under another title which relates to the Conditional Formatting Feature
at the moment, my boxes are

Low = 2 Med = 4 High = 6.

I want the colours to reflect that. I couldn't get the dialogue box to
work
out the sums, so set up 3 boxes to give me the Dmax, Dmin for the
column,
then used the two figures to arrive at a median for the mid value. Then
tell
the Condititional Format If Higher than or equal to [High], make one
colour,
If Lower or Equal to [Low], make the second colour, and if between
[High]
and
[Low] make the 3rd colour for the mid value.


In my Form/table, i have a list of numbers representing the amount of
times
a
user has got a question wrong, based on the field [x_wrong].

for example:

[Qstn_ID] [Xrwong] [Question] Colour

4 15 blah, blah Green
6 9 " Green
18 27 " Red
4 43 " Red
9 4 " Blue
2 8 " Green
33 3 " Blue
9 3 " Blue
84 22 “ Red

I want to be able to colour code the 3 ranges available, based on the
highest 3rd, middle third, and lowest 3rd percentages of the values
using
the
fluctuating highest value, and lowest value as the index. Because the
values
are constantly changing, i cannot use a fixed figure; say >33 because
the
highest value may never reach 33.

:

Difference? But you're dividing! Otherwise Allen's trick still works.

- Int(-([High]-[Low]))

Rod

:

I have an Unbound totals box on a Form Footer that gets the
diference
in
value between two other Totals boxes. But under certain conditions
the
Value
displays as for example, 1.4. How can I round this box value up when
the
result is a fraction?

The default value in the box is:

=[High]/[Low]