Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Significant number rounding based on key cell
I have made this thread as the old one on the rounding subject was
getting nowhere. I have a cell that has a number in it, that can change based on my input, but more importantly its DP can change also. Say 0.1 or 0.01 or 0.0001 etc. This cell is my key cell. I have another cell that has a calculated number in it, (it gives me an answer based on an average of 3 cells.) and I have a macro that I run, to set the DP of this calculated cell to match the key cell's number of DP's. So far all is good. What I need is, for the calculated cell to either round itself up or down to fall in line with the number in the key cell. The key cell is usually always a multiple of 1 or 5 like 0.05 or 0.005 or 0.5 or 1 or 0.01 etc. Problem I have is, I am getting the average cell answer fine, but it often is not in a multiple of the key cell. So I could have the key cell as 0.050 and the average cell as 0.043. I need a macro to round the answer from 0.043 to in this case it would round up to 0.050. Another example could be, Key cell: 0.010 Average cell: 0.023 I need it to round the answer in this case to: 0.020. This will hopefully be an automatic process, but I can put a button on my ssheet to click after all data is entered to make the magic happen. If the key cells content didnt change DP wise and number wise, I could just use the round function and never touch it again, but my key cell changes hence the need to have the rounding function more intelligent. Any help greatly appreciated. Cheers, Aaron. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Significant number rounding based on key cell
One way:
=ROUND(AverageCell/KeyCell, 0) * KeyCell In article . com, "Slashman" wrote: I have made this thread as the old one on the rounding subject was getting nowhere. I have a cell that has a number in it, that can change based on my input, but more importantly its DP can change also. Say 0.1 or 0.01 or 0.0001 etc. This cell is my key cell. I have another cell that has a calculated number in it, (it gives me an answer based on an average of 3 cells.) and I have a macro that I run, to set the DP of this calculated cell to match the key cell's number of DP's. So far all is good. What I need is, for the calculated cell to either round itself up or down to fall in line with the number in the key cell. The key cell is usually always a multiple of 1 or 5 like 0.05 or 0.005 or 0.5 or 1 or 0.01 etc. Problem I have is, I am getting the average cell answer fine, but it often is not in a multiple of the key cell. So I could have the key cell as 0.050 and the average cell as 0.043. I need a macro to round the answer from 0.043 to in this case it would round up to 0.050. Another example could be, Key cell: 0.010 Average cell: 0.023 I need it to round the answer in this case to: 0.020. This will hopefully be an automatic process, but I can put a button on my ssheet to click after all data is entered to make the magic happen. If the key cells content didnt change DP wise and number wise, I could just use the round function and never touch it again, but my key cell changes hence the need to have the rounding function more intelligent. Any help greatly appreciated. Cheers, Aaron. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Significant number rounding based on key cell
Thanks so much for this. Your solution works perfectly.
Cheers, Aaron. JE McGimpsey wrote: One way: =ROUND(AverageCell/KeyCell, 0) * KeyCell In article . com, "Slashman" wrote: I have made this thread as the old one on the rounding subject was getting nowhere. I have a cell that has a number in it, that can change based on my input, but more importantly its DP can change also. Say 0.1 or 0.01 or 0.0001 etc. This cell is my key cell. I have another cell that has a calculated number in it, (it gives me an answer based on an average of 3 cells.) and I have a macro that I run, to set the DP of this calculated cell to match the key cell's number of DP's. So far all is good. What I need is, for the calculated cell to either round itself up or down to fall in line with the number in the key cell. The key cell is usually always a multiple of 1 or 5 like 0.05 or 0.005 or 0.5 or 1 or 0.01 etc. Problem I have is, I am getting the average cell answer fine, but it often is not in a multiple of the key cell. So I could have the key cell as 0.050 and the average cell as 0.043. I need a macro to round the answer from 0.043 to in this case it would round up to 0.050. Another example could be, Key cell: 0.010 Average cell: 0.023 I need it to round the answer in this case to: 0.020. This will hopefully be an automatic process, but I can put a button on my ssheet to click after all data is entered to make the magic happen. If the key cells content didnt change DP wise and number wise, I could just use the round function and never touch it again, but my key cell changes hence the need to have the rounding function more intelligent. Any help greatly appreciated. Cheers, Aaron. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
cut off number in excel cell vs. rounding | Excel Discussion (Misc queries) | |||
formatting cell number based on previous cell number | Excel Discussion (Misc queries) | |||
resetting last cell | Excel Discussion (Misc queries) | |||
Cell color based upon cell value | Excel Discussion (Misc queries) | |||
Rounding up for down based on cell value | Excel Worksheet Functions |