rounding up or down

[Eqa]

I want to have prices that end in #58 or #98. So I need a formula that takes
a cost price of something and multiplies by 2.5 and then rounds it to a price
that ends in 58 or 98. The final price could be as low as 58 and up to a 5
figure number ending in either of those 2 options which ever is the closer.
If a figure were to fall at the half way mark ie 70 then it should round up
to the 98.

Hope this clear and thanks Eqa.

 

[Carim]

Hi,

Think you can achieve your objective with two functions : Floor() and
Ceiling() ...

HTH

 

[Bob Phillips]

58 and 98 seems an odd pair. 48 and 98 I would understand, but not 58.

What is the rationale behind this rounding?

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[joeu2004]

I want to have prices that end in #58 or #98. So I need a formula that takes
a cost price of something and multiplies by 2.5 and then rounds it to a price
that ends in 58 or 98. The final price could be as low as 58 and up to a 5
figure number ending in either of those 2 options which ever is the closer.
If a figure were to fall at the half way mark ie 70 then it should round up
to the 98.

First, I think 78 is halfway between 58 and 98. So I presume you want
numbers ending in 59 through 77 to round down to a number ending in
58, and numbers ending 78 to 97 to round up to a number ending in 98.

Second, what about numbers ending between 98 and 58? Do want numbers
ending in 99 to 27 to round down to a number ending in 98, and numbers
ending in 28 to 57 to round up to a number ending in 58?

Third, referring to 58 and 98, are you talking about the last two
digits of integers? Or are you talking about the first two digits of
a fractional part (e.g. cents)?

Finally, if you are talking about integers, if multiplying by 2.5
results in a fractional part, will you round the result to an integer
before deciding whether to round to 58 or 98? For example, 3111 times
2.5 is 7777.5. Should that round down to 7758 or up to 7798?

Similarly, if you are talking about numbers with fractional parts,
31.11 times 2.5 is 77.775. Should that round down to 77.58 or up to
77.98?

 

[joeu2004]

PS.... (I can't sleep before a long trip ahead of me.)

I want to have prices that end in #58 or #98. So I need a formula that takes
a cost price of something and multiplies by 2.5 and then rounds it to a price
that ends in 58 or 98. The final price could be as low as 58 and up to a 5
figure number ending in either of those 2 options which ever is the closer.
If a figure were to fall at the half way mark ie 70 then it should round up
to the 98.

I agree with Bob about the oddness of the asymmetry of the
requirements. Be that as it may, the following is the best solution I
can think of off-hand. It's messy. I would like to see a
substantially simpler one, if anyone can come up with it. I would
especially like Carim to demonstrate simple solutions with just
CEILING() or FLOOR(). Anyway, here goes....

=if(mod(A1,100)>=78, 100*int(A1/100)+98,
if(mod(A1,100)<28, 100*int(A1/100)-2, 100*int(A1/100)+58))

That assumes that A1 is the number times 2.5, that we want integers
ending in 58 and 98, and that numbers ending in 77.5, for example,
should be rounded down. If we want fractional numbers ending in 0.58
and 0.98, assume that A1 is the number times 250 (i.e. times 2.5 times
100), and divide the formula above by 100 at the end. Alternatively,
of course, the formula can be adjusted to incorporate (and optimize)
those assumed calculations.

Note that the formula fails (i.e. returns -2) for numbers less than
28. We could guard against that by wrapping MAX() around all of it;
for example, MAX(0,IF(...)). But the OP said that the lowest value
will be 58. (Which is one reason why I suspect the OP is looking for
integers ending in 58 and 98.)

 

[Eqa]

WOW.
I am iin China and was told that these are lucky nos. and that 4 was unlucky
which should have been a logical choice. Hope that ans. your queries.
As to the rest the 58 or 98 will only be in an integer and not fractions. I
wanted the 2.5 times the cost price to be a part of the formula. I wont ahve
any number below 28 so the issue of ending with a minus figure is not an
issue.

Eqa

 

[joeu2004]

I am iin China and was told that these are lucky nos. and that 4
was unlucky which should have been a logical choice.

I knew that 8s are lucky -- at least 88. Anyway, are you saying that
58 and 98 specifically are lucky; or are they lucky because they end
in 8?

Just curious. I am very interested in Chinese traditions.

 

[Eqa]

Aparently 4 is not lucky. Hence 58.

Can you help me to include the 2.5 times the cost price into the formula you
suggested. It works fine but I can't add the 2.5 into it.

Thanks,
Eqa

 

[joeu2004]

Can you help me to include the 2.5 times the cost price into the
formula you suggested. It works fine but I can't add the 2.5 into it.

=if(mod(2.5*A1,100)>=78, 100*int(2.5*A1/100)+98,
if(mod(2.5*A1,100)<28, 100*int(2.5*A1/100)-2,
100*int(2.5*A1/100)+58))

By the way, do you realize that I extended your requirements, rounding
integers ending in anything less than 27 down to a number ending in
98? For example, 8827 becomes 8798. You need to ask yourself if that
is what you intended.

 

[joeu2004]

Aparently 4 is not lucky. Hence 58.

What about the number 3? If that is not unlucky, perhaps you could
round to integers ending in 38 and 88. That is nice because the
numbers are evenly spaced. That lends itself to a simpler formula for
rounding, namely:

=mround(A1-38,5) + 38

MROUND is in the Analysis ToolPak, a Microsoft add-in. If you prefer
to use only standard functions, you could write:

=50*round((A1-38)/50,0) + 38

But of course, if the Chinese business community has standardized on
integers ending in 58 and 98, so be it.

 

[Eqa]

That is great it works well. What I have to do if I did want to use say 48
and 98 rather than 58?
BTW I know this is off the track but I have a series of formulas in a series
of cells that are waiting for reference cells to be filled in and therefore
are showing #value isthere something I can do to get rid of this and show
nothing until a value is entered?

Thanks for your help.

Eqa

 

[joeu2004]

=mround(A1-38,5) + 38
[....or....]
=50*round((A1-38)/50,0) + 38

That is great it works well. What I have to do if I did want to use say 48
and 98 rather than 58?

=mround(A1-48,50) + 48

or

=50*round((A1-48)/50,0) + 48

Test using a column of numbers ending in 99 (for example, 1199)
through numbers ending in 98 (for example, 1298). You should see that
numbers ending in 99 through 22 round down to a number ending in 98,
numbers ending in 23 through 72 round up to a number ending in 48, and
numbers ending 73 through 98 round up to a number ending in 98. For
example, 1199 through 1222 become 1198, 1223 through 1272 become 1248,
and 1273 through 1298 become 1298. Note that 23 is halfway between 98
and 48, and 73 is halfway between 48 and 98.

BTW I know this is off the track but I have a series of formulas in a series
of cells that are waiting for reference cells to be filled in and therefore
are showing  #value    isthere something I can do to get rid of thisand show
nothing until a value is entered?

It is better to post new questions in a new thread so that it will
catch the attention of people who might have constructive input.

Generally, the following might avoid the #VALUE error:

=if(A1="", "", ...your formula...)

But it depends on why your formula gets #VALUE. For example, the
above solution would not be sufficient if your formula refers to both
A1 and A2, and you put a value in A1, but A2 is still empty. I
believe the simplest catch-all solution is:

=if(iserror(...your formula...), "", ...your formula...)

Some people object because that hides other mistakes that you might
have.

I suggest that you repost your question in a new thread, and be sure
to include examples of one or more distinctive formulas that are
returning #VALUE.

 

[Eqa]

Thanks for all your help.

Eqa

=mround(A1-38,5) + 38
[....or....]
=50*round((A1-38)/50,0) + 38

That is great it works well. What I have to do if I did want to use say 48
and 98 rather than 58?

=mround(A1-48,50) + 48

or

=50*round((A1-48)/50,0) + 48

Test using a column of numbers ending in 99 (for example, 1199)
through numbers ending in 98 (for example, 1298). You should see that
numbers ending in 99 through 22 round down to a number ending in 98,
numbers ending in 23 through 72 round up to a number ending in 48, and
numbers ending 73 through 98 round up to a number ending in 98. For
example, 1199 through 1222 become 1198, 1223 through 1272 become 1248,
and 1273 through 1298 become 1298. Note that 23 is halfway between 98
and 48, and 73 is halfway between 48 and 98.

BTW I know this is off the track but I have a series of formulas in a series
of cells that are waiting for reference cells to be filled in and therefore
are showing #value isthere something I can do to get rid of this and show
nothing until a value is entered?

It is better to post new questions in a new thread so that it will
catch the attention of people who might have constructive input.

Generally, the following might avoid the #VALUE error:

=if(A1="", "", ...your formula...)

But it depends on why your formula gets #VALUE. For example, the
above solution would not be sufficient if your formula refers to both
A1 and A2, and you put a value in A1, but A2 is still empty. I
believe the simplest catch-all solution is:

=if(iserror(...your formula...), "", ...your formula...)

Some people object because that hides other mistakes that you might
have.

I suggest that you repost your question in a new thread, and be sure
to include examples of one or more distinctive formulas that are
returning #VALUE.