| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Nesting the roundup Function in a larger formula | Excel Worksheet Functions | |||
| Roundup in VBA | Excel Discussion (Misc queries) | |||
| roundup value | Excel Discussion (Misc queries) | |||
| ROundup | Excel Worksheet Functions | |||
| RoundUp | Excel Discussion (Misc queries) | |||
![John F[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Hybrid Mode
