Help with rounding etc.

[Gary Slusser]

Hi all. I'm a total novice at formulas and I hope I outline my problem
sufficiently.

After many hours of trying over a few days, and then reading many posts here
and suggested helpful web sites, I can not get any formula to work, and
other people that I've asked to look at it have given up with glazed
expressions... So any help would be greatly appreciated.

C17 is any number between 1000 and 120000 and C18 =C17/20000 and that number
is always between 0.0 and 6.0.

If C18 is < 1.0 I need to roundup to 1.0 and if it is >=n.3 and <= n.5 I
have to round it up to n.5. At the same time I have to have all n.6 to n.9
to roundup to n.0.

I'm having a very difficult time rounding up and/or down or using any other
function to get the final number displayed only as a: 1, 1.5, 2, 2.5, 3,
3.5, 4, 4.5, 5, or 5.5.

Gary

 

[RagDyer]

Try this in C18:

=MAX(CEILING(C17/20000,0.5),1)
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


Hi all. I'm a total novice at formulas and I hope I outline my problem
sufficiently.

After many hours of trying over a few days, and then reading many posts here
and suggested helpful web sites, I can not get any formula to work, and
other people that I've asked to look at it have given up with glazed
expressions... So any help would be greatly appreciated.

C17 is any number between 1000 and 120000 and C18 =C17/20000 and that number
is always between 0.0 and 6.0.

If C18 is < 1.0 I need to roundup to 1.0 and if it is >=n.3 and <= n.5 I
have to round it up to n.5. At the same time I have to have all n.6 to n.9
to roundup to n.0.

I'm having a very difficult time rounding up and/or down or using any other
function to get the final number displayed only as a: 1, 1.5, 2, 2.5, 3,
3.5, 4, 4.5, 5, or 5.5.

Gary

 

[Ron Rosenfeld]

Hi all. I'm a total novice at formulas and I hope I outline my problem
sufficiently.

After many hours of trying over a few days, and then reading many posts here
and suggested helpful web sites, I can not get any formula to work, and
other people that I've asked to look at it have given up with glazed
expressions... So any help would be greatly appreciated.

C17 is any number between 1000 and 120000 and C18 =C17/20000 and that number
is always between 0.0 and 6.0.

If C18 is < 1.0 I need to roundup to 1.0 and if it is >=n.3 and <= n.5 I
have to round it up to n.5. At the same time I have to have all n.6 to n.9
to roundup to n.0.

I'm having a very difficult time rounding up and/or down or using any other
function to get the final number displayed only as a: 1, 1.5, 2, 2.5, 3,
3.5, 4, 4.5, 5, or 5.5.

Gary

Perhaps something like:

=MAX(1,ROUND(C18*2,0)/2)


--ron

 

[Gary Slusser]

Thanks RD.

Oh soooo close! Where it should display 1.5 is goes up to 2.0.

Also I made a mistake, everything above n.0 must go up to n.5 and everything
above n.5 must go up to n.0.

Gary

 

[Gary Slusser]

Perhaps something like:

=MAX(1,ROUND(C18*2,0)/2)


--ron

YES! It works.

Except I made a mistake. I need everything above n.0 to go up to n.5 and
everything above n.5 to go up to n.0. Is that possible?

Thanks Ron.

Gary

 

[Frank Kabel]

Hi
do you mean
=CEILING(A1,0.5)

or
=MAX(1,CEILING(A1,0.5))

Also take a look at:
http://www.xldynamic.com/source/xld.Rounding.html

 

[Ron Rosenfeld]

YES! It works.

Except I made a mistake. I need everything above n.0 to go up to n.5 and
everything above n.5 to go up to n.0. Is that possible?

Thanks Ron.

=MAX(1,CEILING(C18,0.5))


--ron

 

[Gary Slusser]

Thanks Frank, but that doesn't do it either and I've been to the web site
and tried a number of things but couldn't get it so I posted the problem.

Ron's formula works but it doesn't round the n.1 and n.2 up to n.5 nor the
n.6 up to n.0. But that's what I originally said I wanted. lol Now I see I
need everything above n.0 to go up to n.5 and everything above 1.5 to go up
to n.0. Any ideas are appreciated.

Gary

Bulletin Board www.qualitywaterassociates.com/phpBB2

 

[Gary Slusser]

=MAX(1,CEILING(C18,0.5))


--ron

Hi Ron, thanks it is so close. But now it rounds up to n.0 when C18 is n.5.

Is there any way to not execute the formula when C18 is already n.5 but
display the n.5 in B25? I hope I'm asking that clear enough.

Gary

 

[Frank Kabel]

Hi
best would be to give us real examples of what you want .
Post some values and the expected result for each. Because our ceiling
formulas should do what you want e.g.
0.1 -> 0.5
0.4 -> 0.5
0.6 -> 1.0
....

So please provide in this style your example data and expected result

 

[Frank Kabel]

Hi
this wouldn't round 0.5 to 1.0 if the value in C18 IS really 0.5. Is
this value created by a formula or have you manually entered it. (I
assume the value is something like 0.500001)

 

[Ron Rosenfeld]

Hi Ron, thanks it is so close. But now it rounds up to n.0 when C18 is n.5.

Is there any way to not execute the formula when C18 is already n.5 but
display the n.5 in B25? I hope I'm asking that clear enough.

I do not believe it is doing what you think it is.

In other words, I do not believe that you really have n.5 in the cell. You
most likely have a number that is *between* n.5 and n.55 but your formatting is
such that you are only displaying one digit to the right of the decimal.

In your second request post, you wrote:

=====================
Except I made a mistake. I need everything above n.0 to go up to n.5 and
everything above n.5 to go up to n.0. Is that possible?
====================

Now you seem to want some numbers that are greater than n.5 to round DOWN to
n.5. Or maybe you do not. But you will have to be more specific.


--ron

 

[Gary Slusser]

Hi Frank, thanks again. Here's what it is with sample data before the cell
number.

42 B6, will always contain a manually entered number from 1-150. Max
usually <60.
0.0 B7, will always contain a manually entered number from 0 to 100. Max
usually <30.
0 B8, will always contain a manually entered number from 0 to 10. Max
usually <10
3 C10, will always contain a manually entered number from 1-100. Max
usually <10.
60 C11, will always contain a manually entered number from 10-200. Max
usually <125.
0 C12 will always contain a manually entered number from 0-2500.

42 C13 is =B6+((B7*4)+(B8*2))
180 C14 is =C10*C11+C12
7,560 C15 is =ROUND(C13,0)*C14
60,480 C16 is =C15*8
3.0 C17 is =C16/20000
3.5 C 18 =MAX(1,CEILING(C17,0.5)) is the problem, it should be 3.0.
But it is rounding down all n.5 to n.0 probably due to C15 and my round and
decimal setting as Ron mentions in his latest reply to me below.

I need C18 to show
1.0 when it is <=1
and when it is over 1.0-1.4 to give me a 1.5
and when over 1.5 -1.9 a 2.0 etc. up to 6.5 etc.

I need only 1.0, 1.5, 2.0, 2.5, 3.0, 3.5, 4.0, 4.5, 5.0, 5.5, 6.0 etc.

Gary

 

[Frank Kabel]

Hi
change C17 to
=ROUND(C16/20000,1)

--
Regards
Frank Kabel
Frankfurt, Germany

Hi Frank, thanks again. Here's what it is with sample data before the cell
number.

42 B6, will always contain a manually entered number from 1-150. Max
usually <60.
0.0 B7, will always contain a manually entered number from 0 to 100. Max
usually <30.
0 B8, will always contain a manually entered number from 0 to 10. Max
usually <10
3 C10, will always contain a manually entered number from 1-100. Max
usually <10.
60 C11, will always contain a manually entered number from 10-200. Max
usually <125.
0 C12 will always contain a manually entered number from 0-2500.

42 C13 is =B6+((B7*4)+(B8*2))
180 C14 is =C10*C11+C12
7,560 C15 is =ROUND(C13,0)*C14
60,480 C16 is =C15*8
3.0 C17 is =C16/20000
3.5 C 18 =MAX(1,CEILING(C17,0.5)) is the problem, it should be 3.0.
But it is rounding down all n.5 to n.0 probably due to C15 and my round and
decimal setting as Ron mentions in his latest reply to me below.

I need C18 to show
1.0 when it is <=1
and when it is over 1.0-1.4 to give me a 1.5
and when over 1.5 -1.9 a 2.0 etc. up to 6.5 etc.

I need only 1.0, 1.5, 2.0, 2.5, 3.0, 3.5, 4.0, 4.5, 5.0, 5.5, 6.0 etc.

Gary

 

[Gary Slusser]

I do not believe it is doing what you think it is.

In other words, I do not believe that you really have n.5 in the cell.
You
most likely have a number that is *between* n.5 and n.55 but your
formatting is
such that you are only displaying one digit to the right of the decimal.

In your second request post, you wrote:

=====================
Except I made a mistake. I need everything above n.0 to go up to n.5 and
everything above n.5 to go up to n.0. Is that possible?
====================

Now you seem to want some numbers that are greater than n.5 to round DOWN
to
n.5. Or maybe you do not. But you will have to be more specific.


--ron

Hi Ron, thanks again and sorry for the confusion. Hopefully I'll finally get
my explanation accurate... I messed it up again in my reply to Frank above!

Frank here is the corrected version.

Here's what it is with actual data before the cell number.

42 B6, will always contain a manually entered number from 1-150. Max
usually <60.
0.0 B7, will always contain a manually entered number from 0 to 100. Max
usually <30.
0 B8, will always contain a manually entered number from 0 to 10. Max
usually <10
3 C10, will always contain a manually entered number from 1-100. Max
usually <10.
60 C11, will always contain a manually entered number from 10-200. Max
usually <125.
0 C12 will always contain a manually entered number from 0-2500.

42 C13 is =B6+((B7*4)+(B8*2))
180 C14 is =C10*C11+C12
7,560 C15 is =ROUND(C13,0)*C14
60,480 C16 is =C15*8
3.0 C17 is =C16/20000
3.5 C 18 =MAX(1,CEILING(C17,0.5)) is the problem, it should be 3.0.

C17 is 3.0 and I need C18 to be 3.0 instead of the 3.5 it is.

But it is rounding down the n.5 numbers to n.0, probably due to C15 and my
rounding and decimal setting?

I need C18 to display:
1.0 when it is .1 to .9
and when it is 1.1-1.4 to give me a 1.5
and when it is 1.6 -1.9 to give me a 2.0 etc. up to 6.5 etc. No rounding
down, only up.
In C18 I need only 1.0, 1.5, 2.0, 2.5, 3.0, 3.5, 4.0, 4.5, 5.0, 5.5, 6.0
etc. in C18

Gary

 

[Gary Slusser]

I do not believe it is doing what you think it is.

In other words, I do not believe that you really have n.5 in the cell.
You
most likely have a number that is *between* n.5 and n.55 but your
formatting is
such that you are only displaying one digit to the right of the decimal.

In your second request post, you wrote:

=====================
Except I made a mistake. I need everything above n.0 to go up to n.5 and
everything above n.5 to go up to n.0. Is that possible?
====================

Now you seem to want some numbers that are greater than n.5 to round DOWN
to
n.5. Or maybe you do not. But you will have to be more specific.


--ron

Ron, I fixed it by changing all the cell formats to General. It works just
as I wanted. Thank you greatly.

Gary

 

[Ron Rosenfeld]

C17 is 3.0 and I need C18 to be 3.0 instead of the 3.5 it is.

Given the numbers you have posted, C17 is NOT 3.0. Rather it is 3.024.

Since 3.024 is greater than 3.0, it will round up given the formulas.

If this is not desired behavior, you need to post exactly what you want to
occur for values *between* n.0 and n.1



--ron

 

[Ron Rosenfeld]

Ron, I fixed it by changing all the cell formats to General. It works just
as I wanted. Thank you greatly.

Ah, glad to read you worked it out.

As you have discovered, changing the format does not change the value actually
stored in the cell (unless you have enabled the option Precision as Displayed).
It only changes the value displayed.


--ron

 

[Gary Slusser]

Frank, Thanks you very much for the help. I got it to work correctly with
Ron's formula and changing all the cell formats to General instead of number
with decimals set.

Gary

Hi
change C17 to
=ROUND(C16/20000,1)