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.

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.

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.