A rounding function
 

I am just wondering if you are able to have two functions in one cell.....

However, this is probably more complicated, as I would like to round up to
the nearest 5, rather to the nearest 10?!!!

Scenario, in my cell at the moment, I have the sum function, adding a few
columns across, giving me the answer I need. Now, in addition to this sum
function, I would also like the answer to be rounded according to the
following....

Should the amount be either 0.1 or 3.2 (ie .1 or .2), the amount be rounded
down (ie 0.0 or 3.0), and if the figures are 0.3 or 5.4 (ie .3 or .4) the
amount be rounded up to the nearest 5 (ie 0.5 or 5.5)

In addition if the amounts were .6 or .7 rounded down to the nearest 5 and
..8 or .9 rounded up to the nearest 5.

Does that make sense???

I guess the best option would possibly be an if formula? but then how do I
keep the initial sum formula as part of it all???

Any help would be very greatly appreciated!

A rounding function
 

=MROUND(SUM(A1:C20),0.5)
Adjust range as needed

"Shaz" wrote:

I am just wondering if you are able to have two functions in one cell.....

However, this is probably more complicated, as I would like to round up to
the nearest 5, rather to the nearest 10?!!!

Scenario, in my cell at the moment, I have the sum function, adding a few
columns across, giving me the answer I need. Now, in addition to this sum
function, I would also like the answer to be rounded according to the
following....

Should the amount be either 0.1 or 3.2 (ie .1 or .2), the amount be rounded
down (ie 0.0 or 3.0), and if the figures are 0.3 or 5.4 (ie .3 or .4) the
amount be rounded up to the nearest 5 (ie 0.5 or 5.5)

In addition if the amounts were .6 or .7 rounded down to the nearest 5 and
.8 or .9 rounded up to the nearest 5.

Does that make sense???

I guess the best option would possibly be an if formula? but then how do I
keep the initial sum formula as part of it all???

Any help would be very greatly appreciated!

A rounding function
 

Try it like this:

=ROUND(SUM(C2:G2)/0.5,0)*0.5

--
Biff
Microsoft Excel MVP


"Shaz" wrote in message
...
I am just wondering if you are able to have two functions in one cell.....

However, this is probably more complicated, as I would like to round up to
the nearest 5, rather to the nearest 10?!!!

Scenario, in my cell at the moment, I have the sum function, adding a few
columns across, giving me the answer I need. Now, in addition to this sum
function, I would also like the answer to be rounded according to the
following....

Should the amount be either 0.1 or 3.2 (ie .1 or .2), the amount be
rounded
down (ie 0.0 or 3.0), and if the figures are 0.3 or 5.4 (ie .3 or .4) the
amount be rounded up to the nearest 5 (ie 0.5 or 5.5)

In addition if the amounts were .6 or .7 rounded down to the nearest 5 and
.8 or .9 rounded up to the nearest 5.

Does that make sense???

I guess the best option would possibly be an if formula? but then how do
I
keep the initial sum formula as part of it all???

Any help would be very greatly appreciated!

A rounding function
 

Great! I think MROUND doesn't work with xl2003.
You could also multiply by 2 and divide by 2.


"T. Valko" wrote:

Try it like this:

=ROUND(SUM(C2:G2)/0.5,0)*0.5

--
Biff
Microsoft Excel MVP


"Shaz" wrote in message
...
I am just wondering if you are able to have two functions in one cell.....

However, this is probably more complicated, as I would like to round up to
the nearest 5, rather to the nearest 10?!!!

Scenario, in my cell at the moment, I have the sum function, adding a few
columns across, giving me the answer I need. Now, in addition to this sum
function, I would also like the answer to be rounded according to the
following....

Should the amount be either 0.1 or 3.2 (ie .1 or .2), the amount be
rounded
down (ie 0.0 or 3.0), and if the figures are 0.3 or 5.4 (ie .3 or .4) the
amount be rounded up to the nearest 5 (ie 0.5 or 5.5)

In addition if the amounts were .6 or .7 rounded down to the nearest 5 and
.8 or .9 rounded up to the nearest 5.

Does that make sense???

I guess the best option would possibly be an if formula? but then how do
I
keep the initial sum formula as part of it all???

Any help would be very greatly appreciated!

A rounding function
 

I think MROUND doesn't work with xl2003

MROUND is part of the Analysis ToolPak add-in. As long as the add-in is
loaded it should work.

That's why I use the method I posted. Don't have to worry about the ATP.

--
Biff
Microsoft Excel MVP


"Tevuna" wrote in message
...
Great! I think MROUND doesn't work with xl2003.
You could also multiply by 2 and divide by 2.


"T. Valko" wrote:

Try it like this:

=ROUND(SUM(C2:G2)/0.5,0)*0.5

--
Biff
Microsoft Excel MVP


"Shaz" wrote in message
...
I am just wondering if you are able to have two functions in one
cell.....

However, this is probably more complicated, as I would like to round up
to
the nearest 5, rather to the nearest 10?!!!

Scenario, in my cell at the moment, I have the sum function, adding a
few
columns across, giving me the answer I need. Now, in addition to this
sum
function, I would also like the answer to be rounded according to the
following....

Should the amount be either 0.1 or 3.2 (ie .1 or .2), the amount be
rounded
down (ie 0.0 or 3.0), and if the figures are 0.3 or 5.4 (ie .3 or .4)
the
amount be rounded up to the nearest 5 (ie 0.5 or 5.5)

In addition if the amounts were .6 or .7 rounded down to the nearest 5
and
.8 or .9 rounded up to the nearest 5.

Does that make sense???

I guess the best option would possibly be an if formula? but then how
do
I
keep the initial sum formula as part of it all???

Any help would be very greatly appreciated!