Roundup Nesting
 

John F wrote:
Help !! Another novice.
Excel 2003

Looking to nest 3 roundup statements.

While in cell K?, look to Cell J? ( one cell to left)
If J? <= 10.00, Roundup (J?,1)
If J? 10.00 and <= 1,000.00, Roundup (J?,0)
If J? 1,000.00, Roundup (J?,-1)

To make it more interesting, the nested instructions
should then "loop", to operate in each "K?" cell,
to end of file.

How would it define what is the end of the file?, as
there are a number of (sets of) blank cells that it
will have to "process" within the coloumn.

I hope to set up the final macro on a button
I just did one for a footer macro I made.

I hope this is enough detail for someone to help me out.
Thanks in advance.

John F. Scholten

Roundup Nesting
 

Hi
maybe without a macro. Use the following formula in K1
=ROUNDUP(J1,1-(J110)-(J11000))
and copy this down for all rows

--
Regards
Frank Kabel
Frankfurt, Germany

"John F" schrieb im Newsbeitrag
...
John F wrote:
Help !! Another novice.
Excel 2003

Looking to nest 3 roundup statements.

While in cell K?, look to Cell J? ( one cell to left)
If J? <= 10.00, Roundup (J?,1)
If J? 10.00 and <= 1,000.00, Roundup (J?,0)
If J? 1,000.00, Roundup (J?,-1)

To make it more interesting, the nested instructions
should then "loop", to operate in each "K?" cell,
to end of file.

How would it define what is the end of the file?, as
there are a number of (sets of) blank cells that it
will have to "process" within the coloumn.

I hope to set up the final macro on a button
I just did one for a footer macro I made.

I hope this is enough detail for someone to help me out.
Thanks in advance.

John F. Scholten

Roundup Nesting
 

John,

Here is a shot

Dim cRows As Long
Dim i As Long

cRows = Cells(Rows.Count, "K").End(xlUp).Row
For i = 1 To cRows
If Cells(i, "J").Value <= 10 Then
Cells(i, "J").Valu = WorksheetFunction.RoundUp(Cells(i,
"J").Value, 1)
ElseIf Cells(i, "J").Value <= 1000 Then
Cells(i, "J").Value = WorksheetFunction.RoundUp(Cells(i,
"J").Value, 0)
Else
Cells(i, "J").Value = WorksheetFunction.RoundUp(Cells(i,
"J").Value, -1)
End If
Next i

--

HTH

RP

"John F" wrote in message
...
John F wrote:
Help !! Another novice.
Excel 2003

Looking to nest 3 roundup statements.

While in cell K?, look to Cell J? ( one cell to left)
If J? <= 10.00, Roundup (J?,1)
If J? 10.00 and <= 1,000.00, Roundup (J?,0)
If J? 1,000.00, Roundup (J?,-1)

To make it more interesting, the nested instructions
should then "loop", to operate in each "K?" cell,
to end of file.

How would it define what is the end of the file?, as
there are a number of (sets of) blank cells that it
will have to "process" within the coloumn.

I hope to set up the final macro on a button
I just did one for a footer macro I made.

I hope this is enough detail for someone to help me out.
Thanks in advance.

John F. Scholten

Roundup Nesting
 

Frank, you're a genius.
Plain and simple is the way I like it.
Unfortunately, I can't "read" it, but I will work away at that.

However, I did overlooked 2 things.
1: Negative numbers should follow the same type of rounding
2: If NO numbers in certain cells in column J, can the result in K
Show as blank?

"Frank Kabel" wrote:

Hi
maybe without a macro. Use the following formula in K1
=ROUNDUP(J1,1-(J110)-(J11000))
and copy this down for all rows

--
Regards
Frank Kabel
Frankfurt, Germany

"John F" schrieb im Newsbeitrag
...
John F wrote:
Help !! Another novice.
Excel 2003

Looking to nest 3 roundup statements.

While in cell K?, look to Cell J? ( one cell to left)
If J? <= 10.00, Roundup (J?,1)
If J? 10.00 and <= 1,000.00, Roundup (J?,0)
If J? 1,000.00, Roundup (J?,-1)

To make it more interesting, the nested instructions
should then "loop", to operate in each "K?" cell,
to end of file.

How would it define what is the end of the file?, as
there are a number of (sets of) blank cells that it
will have to "process" within the coloumn.

I hope to set up the final macro on a button
I just did one for a footer macro I made.

I hope this is enough detail for someone to help me out.
Thanks in advance.

John F. Scholten

Roundup Nesting
 

Hi
try:
=IF(J1<"",ROUNDUP(J1,1-(ABS(J1)10)-(ABS(J1)1000)),"")

--
Regards
Frank Kabel
Frankfurt, Germany

"John F" schrieb im Newsbeitrag
...
Frank, you're a genius.
Plain and simple is the way I like it.
Unfortunately, I can't "read" it, but I will work away at that.

However, I did overlooked 2 things.
1: Negative numbers should follow the same type of rounding
2: If NO numbers in certain cells in column J, can the result in K
Show as blank?

"Frank Kabel" wrote:

Hi
maybe without a macro. Use the following formula in K1
=ROUNDUP(J1,1-(J110)-(J11000))
and copy this down for all rows

--
Regards
Frank Kabel
Frankfurt, Germany

"John F" schrieb im Newsbeitrag
...
John F wrote:
Help !! Another novice.
Excel 2003

Looking to nest 3 roundup statements.

While in cell K?, look to Cell J? ( one cell to left)
If J? <= 10.00, Roundup (J?,1)
If J? 10.00 and <= 1,000.00, Roundup (J?,0)
If J? 1,000.00, Roundup (J?,-1)

To make it more interesting, the nested instructions
should then "loop", to operate in each "K?" cell,
to end of file.

How would it define what is the end of the file?, as
there are a number of (sets of) blank cells that it
will have to "process" within the coloumn.

I hope to set up the final macro on a button
I just did one for a footer macro I made.

I hope this is enough detail for someone to help me out.
Thanks in advance.

John F. Scholten

Roundup Nesting
 

Frank,

I tried that, but is it what is wanted. It changes a negative number to a
positive, whereas (I think) the OP wants it to stay negative but follow the
rounding rules?

Bob

"Frank Kabel" wrote in message
...
Hi
try:
=IF(J1<"",ROUNDUP(J1,1-(ABS(J1)10)-(ABS(J1)1000)),"")

--
Regards
Frank Kabel
Frankfurt, Germany

"John F" schrieb im Newsbeitrag
...
Frank, you're a genius.
Plain and simple is the way I like it.
Unfortunately, I can't "read" it, but I will work away at that.

However, I did overlooked 2 things.
1: Negative numbers should follow the same type of rounding
2: If NO numbers in certain cells in column J, can the result in K
Show as blank?

"Frank Kabel" wrote:

Hi
maybe without a macro. Use the following formula in K1
=ROUNDUP(J1,1-(J110)-(J11000))
and copy this down for all rows

--
Regards
Frank Kabel
Frankfurt, Germany

"John F" schrieb im Newsbeitrag
...
John F wrote:
Help !! Another novice.
Excel 2003

Looking to nest 3 roundup statements.

While in cell K?, look to Cell J? ( one cell to left)
If J? <= 10.00, Roundup (J?,1)
If J? 10.00 and <= 1,000.00, Roundup (J?,0)
If J? 1,000.00, Roundup (J?,-1)

To make it more interesting, the nested instructions
should then "loop", to operate in each "K?" cell,
to end of file.

How would it define what is the end of the file?, as
there are a number of (sets of) blank cells that it
will have to "process" within the coloumn.

I hope to set up the final macro on a button
I just did one for a footer macro I made.

I hope this is enough detail for someone to help me out.
Thanks in advance.

John F. Scholten

Roundup Nesting
 

Hi Bob
for me it leaves a negative number unchanged? e.g.
-1.234
results in
-1.3


--
Regards
Frank Kabel
Frankfurt, Germany

"Bob Phillips" schrieb im
Newsbeitrag ...
Frank,

I tried that, but is it what is wanted. It changes a negative number
to a
positive, whereas (I think) the OP wants it to stay negative but
follow the
rounding rules?

Bob

"Frank Kabel" wrote in message
...
Hi
try:
=IF(J1<"",ROUNDUP(J1,1-(ABS(J1)10)-(ABS(J1)1000)),"")

--
Regards
Frank Kabel
Frankfurt, Germany

"John F" schrieb im Newsbeitrag
...
Frank, you're a genius.
Plain and simple is the way I like it.
Unfortunately, I can't "read" it, but I will work away at that.

However, I did overlooked 2 things.
1: Negative numbers should follow the same type of rounding
2: If NO numbers in certain cells in column J, can the result in
K
Show as blank?

"Frank Kabel" wrote:

Hi
maybe without a macro. Use the following formula in K1
=ROUNDUP(J1,1-(J110)-(J11000))
and copy this down for all rows

--
Regards
Frank Kabel
Frankfurt, Germany

"John F" schrieb im
Newsbeitrag
...
John F wrote:
Help !! Another novice.
Excel 2003

Looking to nest 3 roundup statements.

While in cell K?, look to Cell J? ( one cell to left)
If J? <= 10.00, Roundup (J?,1)
If J? 10.00 and <= 1,000.00, Roundup (J?,0)
If J? 1,000.00, Roundup (J?,-1)

To make it more interesting, the nested instructions
should then "loop", to operate in each "K?" cell,
to end of file.

How would it define what is the end of the file?, as
there are a number of (sets of) blank cells that it
will have to "process" within the coloumn.

I hope to set up the final macro on a button
I just did one for a footer macro I made.

I hope this is enough detail for someone to help me out.
Thanks in advance.

John F. Scholten

Roundup Nesting
 

oops, sorry my mistake, I didn't spot you corrected my pathetic effort.

Bob

"Frank Kabel" wrote in message
...
Hi Bob
for me it leaves a negative number unchanged? e.g.
-1.234
results in
-1.3


--
Regards
Frank Kabel
Frankfurt, Germany

"Bob Phillips" schrieb im
Newsbeitrag ...
Frank,

I tried that, but is it what is wanted. It changes a negative number
to a
positive, whereas (I think) the OP wants it to stay negative but
follow the
rounding rules?

Bob

"Frank Kabel" wrote in message
...
Hi
try:
=IF(J1<"",ROUNDUP(J1,1-(ABS(J1)10)-(ABS(J1)1000)),"")

--
Regards
Frank Kabel
Frankfurt, Germany

"John F" schrieb im Newsbeitrag
...
Frank, you're a genius.
Plain and simple is the way I like it.
Unfortunately, I can't "read" it, but I will work away at that.

However, I did overlooked 2 things.
1: Negative numbers should follow the same type of rounding
2: If NO numbers in certain cells in column J, can the result in
K
Show as blank?

"Frank Kabel" wrote:

Hi
maybe without a macro. Use the following formula in K1
=ROUNDUP(J1,1-(J110)-(J11000))
and copy this down for all rows

--
Regards
Frank Kabel
Frankfurt, Germany

"John F" schrieb im
Newsbeitrag
...
John F wrote:
Help !! Another novice.
Excel 2003

Looking to nest 3 roundup statements.

While in cell K?, look to Cell J? ( one cell to left)
If J? <= 10.00, Roundup (J?,1)
If J? 10.00 and <= 1,000.00, Roundup (J?,0)
If J? 1,000.00, Roundup (J?,-1)

To make it more interesting, the nested instructions
should then "loop", to operate in each "K?" cell,
to end of file.

How would it define what is the end of the file?, as
there are a number of (sets of) blank cells that it
will have to "process" within the coloumn.

I hope to set up the final macro on a button
I just did one for a footer macro I made.

I hope this is enough detail for someone to help me out.
Thanks in advance.

John F. Scholten

Roundup Nesting
 

Hi Bob
you know while you prefer VBA I prefer (complicated) formulas <vbg



--
Regards
Frank Kabel
Frankfurt, Germany

"Bob Phillips" schrieb im
Newsbeitrag ...
oops, sorry my mistake, I didn't spot you corrected my pathetic
effort.

Bob

"Frank Kabel" wrote in message
...
Hi Bob
for me it leaves a negative number unchanged? e.g.
-1.234
results in
-1.3


--
Regards
Frank Kabel
Frankfurt, Germany

"Bob Phillips" schrieb im
Newsbeitrag ...
Frank,

I tried that, but is it what is wanted. It changes a negative
number
to a
positive, whereas (I think) the OP wants it to stay negative but
follow the
rounding rules?

Bob

"Frank Kabel" wrote in message
...
Hi
try:
=IF(J1<"",ROUNDUP(J1,1-(ABS(J1)10)-(ABS(J1)1000)),"")

--
Regards
Frank Kabel
Frankfurt, Germany

"John F" schrieb im
Newsbeitrag
...
Frank, you're a genius.
Plain and simple is the way I like it.
Unfortunately, I can't "read" it, but I will work away at
that.

However, I did overlooked 2 things.
1: Negative numbers should follow the same type of rounding
2: If NO numbers in certain cells in column J, can the
result in
K
Show as blank?

"Frank Kabel" wrote:

Hi
maybe without a macro. Use the following formula in K1
=ROUNDUP(J1,1-(J110)-(J11000))
and copy this down for all rows

--
Regards
Frank Kabel
Frankfurt, Germany

"John F" schrieb im
Newsbeitrag
...
John F wrote:
Help !! Another novice.
Excel 2003

Looking to nest 3 roundup statements.

While in cell K?, look to Cell J? ( one cell to left)
If J? <= 10.00, Roundup (J?,1)
If J? 10.00 and <= 1,000.00, Roundup (J?,0)
If J? 1,000.00, Roundup (J?,-1)

To make it more interesting, the nested instructions
should then "loop", to operate in each "K?" cell,
to end of file.

How would it define what is the end of the file?, as
there are a number of (sets of) blank cells that it
will have to "process" within the coloumn.

I hope to set up the final macro on a button
I just did one for a footer macro I made.

I hope this is enough detail for someone to help me
out.
Thanks in advance.

John F. Scholten

Roundup Nesting
 

Frank:
Bob is correct. I need the numbers to stay either positive or negative,
but follow the rounding rules regardless.
Blanks should show as blanks, NOT "0"'s, if possible.

Sorry I'm not making myself clear enough.

Thanks.


"Frank Kabel" wrote:

Hi
try:
=IF(J1<"",ROUNDUP(J1,1-(ABS(J1)10)-(ABS(J1)1000)),"")

--
Regards
Frank Kabel
Frankfurt, Germany

"John F" schrieb im Newsbeitrag
...
Frank, you're a genius.
Plain and simple is the way I like it.
Unfortunately, I can't "read" it, but I will work away at that.

However, I did overlooked 2 things.
1: Negative numbers should follow the same type of rounding
2: If NO numbers in certain cells in column J, can the result in K
Show as blank?

"Frank Kabel" wrote:

Hi
maybe without a macro. Use the following formula in K1
=ROUNDUP(J1,1-(J110)-(J11000))
and copy this down for all rows

--
Regards
Frank Kabel
Frankfurt, Germany

"John F" schrieb im Newsbeitrag
...
John F wrote:
Help !! Another novice.
Excel 2003

Looking to nest 3 roundup statements.

While in cell K?, look to Cell J? ( one cell to left)
If J? <= 10.00, Roundup (J?,1)
If J? 10.00 and <= 1,000.00, Roundup (J?,0)
If J? 1,000.00, Roundup (J?,-1)

To make it more interesting, the nested instructions
should then "loop", to operate in each "K?" cell,
to end of file.

How would it define what is the end of the file?, as
there are a number of (sets of) blank cells that it
will have to "process" within the coloumn.

I hope to set up the final macro on a button
I just did one for a footer macro I made.

I hope this is enough detail for someone to help me out.
Thanks in advance.

John F. Scholten

Roundup Nesting
 

Bob:
I'm trying Franks idea for now.
Since I'm new at this, I'm trying the path of last resistance.
I'm too easily intimidated by VBA, at this time.
I've printing your answer off, to keep it on file.
Thanks


"Bob Phillips" wrote:

John,

Here is a shot

Dim cRows As Long
Dim i As Long

cRows = Cells(Rows.Count, "K").End(xlUp).Row
For i = 1 To cRows
If Cells(i, "J").Value <= 10 Then
Cells(i, "J").Valu = WorksheetFunction.RoundUp(Cells(i,
"J").Value, 1)
ElseIf Cells(i, "J").Value <= 1000 Then
Cells(i, "J").Value = WorksheetFunction.RoundUp(Cells(i,
"J").Value, 0)
Else
Cells(i, "J").Value = WorksheetFunction.RoundUp(Cells(i,
"J").Value, -1)
End If
Next i

--

HTH

RP

"John F" wrote in message
...
John F wrote:
Help !! Another novice.
Excel 2003

Looking to nest 3 roundup statements.

While in cell K?, look to Cell J? ( one cell to left)
If J? <= 10.00, Roundup (J?,1)
If J? 10.00 and <= 1,000.00, Roundup (J?,0)
If J? 1,000.00, Roundup (J?,-1)

To make it more interesting, the nested instructions
should then "loop", to operate in each "K?" cell,
to end of file.

How would it define what is the end of the file?, as
there are a number of (sets of) blank cells that it
will have to "process" within the coloumn.

I hope to set up the final macro on a button
I just did one for a footer macro I made.

I hope this is enough detail for someone to help me out.
Thanks in advance.

John F. Scholten

Roundup Nesting
 

Hi
try the formula :-)

--
Regards
Frank Kabel
Frankfurt, Germany

"John F" schrieb im Newsbeitrag
...
Frank:
Bob is correct. I need the numbers to stay either positive or
negative,
but follow the rounding rules regardless.
Blanks should show as blanks, NOT "0"'s, if possible.

Sorry I'm not making myself clear enough.

Thanks.


"Frank Kabel" wrote:

Hi
try:
=IF(J1<"",ROUNDUP(J1,1-(ABS(J1)10)-(ABS(J1)1000)),"")

--
Regards
Frank Kabel
Frankfurt, Germany

"John F" schrieb im Newsbeitrag
...
Frank, you're a genius.
Plain and simple is the way I like it.
Unfortunately, I can't "read" it, but I will work away at that.

However, I did overlooked 2 things.
1: Negative numbers should follow the same type of rounding
2: If NO numbers in certain cells in column J, can the result in
K
Show as blank?

"Frank Kabel" wrote:

Hi
maybe without a macro. Use the following formula in K1
=ROUNDUP(J1,1-(J110)-(J11000))
and copy this down for all rows

--
Regards
Frank Kabel
Frankfurt, Germany

"John F" schrieb im
Newsbeitrag
...
John F wrote:
Help !! Another novice.
Excel 2003

Looking to nest 3 roundup statements.

While in cell K?, look to Cell J? ( one cell to left)
If J? <= 10.00, Roundup (J?,1)
If J? 10.00 and <= 1,000.00, Roundup (J?,0)
If J? 1,000.00, Roundup (J?,-1)

To make it more interesting, the nested instructions
should then "loop", to operate in each "K?" cell,
to end of file.

How would it define what is the end of the file?, as
there are a number of (sets of) blank cells that it
will have to "process" within the coloumn.

I hope to set up the final macro on a button
I just did one for a footer macro I made.

I hope this is enough detail for someone to help me out.
Thanks in advance.

John F. Scholten

Roundup Nesting
 

I just thought that as you posted to .programming you wanted a VBA solution.
Personally I would use the formula too<g

Bob

"John F" wrote in message
...
Bob:
I'm trying Franks idea for now.
Since I'm new at this, I'm trying the path of last resistance.
I'm too easily intimidated by VBA, at this time.
I've printing your answer off, to keep it on file.
Thanks


"Bob Phillips" wrote:

John,

Here is a shot

Dim cRows As Long
Dim i As Long

cRows = Cells(Rows.Count, "K").End(xlUp).Row
For i = 1 To cRows
If Cells(i, "J").Value <= 10 Then
Cells(i, "J").Valu = WorksheetFunction.RoundUp(Cells(i,
"J").Value, 1)
ElseIf Cells(i, "J").Value <= 1000 Then
Cells(i, "J").Value = WorksheetFunction.RoundUp(Cells(i,
"J").Value, 0)
Else
Cells(i, "J").Value = WorksheetFunction.RoundUp(Cells(i,
"J").Value, -1)
End If
Next i

--

HTH

RP

"John F" wrote in message
...
John F wrote:
Help !! Another novice.
Excel 2003

Looking to nest 3 roundup statements.

While in cell K?, look to Cell J? ( one cell to left)
If J? <= 10.00, Roundup (J?,1)
If J? 10.00 and <= 1,000.00, Roundup (J?,0)
If J? 1,000.00, Roundup (J?,-1)

To make it more interesting, the nested instructions
should then "loop", to operate in each "K?" cell,
to end of file.

How would it define what is the end of the file?, as
there are a number of (sets of) blank cells that it
will have to "process" within the coloumn.

I hope to set up the final macro on a button
I just did one for a footer macro I made.

I hope this is enough detail for someone to help me out.
Thanks in advance.

John F. Scholten

Roundup Nesting
 

Hi, again.
Works like a dream - Tested all the combinations I could think of,
Including positives and negatives.
Is now in use in my spread sheet.
Thanks again.

"Frank Kabel" wrote:

Hi
try the formula :-)

--
Regards
Frank Kabel
Frankfurt, Germany

"John F" schrieb im Newsbeitrag
...
Frank:
Bob is correct. I need the numbers to stay either positive or
negative,
but follow the rounding rules regardless.
Blanks should show as blanks, NOT "0"'s, if possible.

Sorry I'm not making myself clear enough.

Thanks.


"Frank Kabel" wrote:

Hi
try:
=IF(J1<"",ROUNDUP(J1,1-(ABS(J1)10)-(ABS(J1)1000)),"")

--
Regards
Frank Kabel
Frankfurt, Germany

"John F" schrieb im Newsbeitrag
...
Frank, you're a genius.
Plain and simple is the way I like it.
Unfortunately, I can't "read" it, but I will work away at that.

However, I did overlooked 2 things.
1: Negative numbers should follow the same type of rounding
2: If NO numbers in certain cells in column J, can the result in
K
Show as blank?

"Frank Kabel" wrote:

Hi
maybe without a macro. Use the following formula in K1
=ROUNDUP(J1,1-(J110)-(J11000))
and copy this down for all rows

--
Regards
Frank Kabel
Frankfurt, Germany

"John F" schrieb im
Newsbeitrag
...
John F wrote:
Help !! Another novice.
Excel 2003

Looking to nest 3 roundup statements.

While in cell K?, look to Cell J? ( one cell to left)
If J? <= 10.00, Roundup (J?,1)
If J? 10.00 and <= 1,000.00, Roundup (J?,0)
If J? 1,000.00, Roundup (J?,-1)

To make it more interesting, the nested instructions
should then "loop", to operate in each "K?" cell,
to end of file.

How would it define what is the end of the file?, as
there are a number of (sets of) blank cells that it
will have to "process" within the coloumn.

I hope to set up the final macro on a button
I just did one for a footer macro I made.

I hope this is enough detail for someone to help me out.
Thanks in advance.

John F. Scholten