MROUND and negative/positive numbers

[Kyle]

I have a spreadsheet where I keep track of certain expenses, and The
Powers That Be want the dollars rounded to the nearest hundred, which
is simple enough using MROUND.

But here's where I run into trouble: in a couple of the cells, I could
run a surplus or debt in any give quarter, which means the number could
be positive or negative. MROUND to the nearest 100 is fine, but it
can't round if the number is negative.

Is there some way of using MROUND or some other function that would
automatically round to the nearest 100 if the number is positive and to
the nearest -100 if the number is negative?

[Chip Pearson]

Try

=MROUND(ABS(A1),100)*SIGN(A1)


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Kyle" <acts2024@gmail.com> wrote in message
news:1141938433.289478.81670@j33g2000cwa.googlegroups.com...
>I have a spreadsheet where I keep track of certain expenses, and
>The
> Powers That Be want the dollars rounded to the nearest hundred,
> which
> is simple enough using MROUND.
>
> But here's where I run into trouble: in a couple of the cells,
> I could
> run a surplus or debt in any give quarter, which means the
> number could
> be positive or negative. MROUND to the nearest 100 is fine, but
> it
> can't round if the number is negative.
>
> Is there some way of using MROUND or some other function that
> would
> automatically round to the nearest 100 if the number is
> positive and to
> the nearest -100 if the number is negative?
>

[Niek Otten]

=MROUND(ABS(A1),100)*IF(A1<0,-1,1)

--
Kind regards,

Niek Otten

"Kyle" <acts2024@gmail.com> wrote in message
news:1141938433.289478.81670@j33g2000cwa.googlegroups.com...
>I have a spreadsheet where I keep track of certain expenses, and The
> Powers That Be want the dollars rounded to the nearest hundred, which
> is simple enough using MROUND.
>
> But here's where I run into trouble: in a couple of the cells, I could
> run a surplus or debt in any give quarter, which means the number could
> be positive or negative. MROUND to the nearest 100 is fine, but it
> can't round if the number is negative.
>
> Is there some way of using MROUND or some other function that would
> automatically round to the nearest 100 if the number is positive and to
> the nearest -100 if the number is negative?
>

Hi

Try something like this:
=IF(E22>0,MROUND(E22,100),-MROUND(-E22,100))

Andy

"Kyle" <acts2024@gmail.com> wrote in message
news:1141938433.289478.81670@j33g2000cwa.googlegroups.com...
>I have a spreadsheet where I keep track of certain expenses, and The
> Powers That Be want the dollars rounded to the nearest hundred, which
> is simple enough using MROUND.
>
> But here's where I run into trouble: in a couple of the cells, I could
> run a surplus or debt in any give quarter, which means the number could
> be positive or negative. MROUND to the nearest 100 is fine, but it
> can't round if the number is negative.
>
> Is there some way of using MROUND or some other function that would
> automatically round to the nearest 100 if the number is positive and to
> the nearest -100 if the number is negative?
>

[Kyle]

All three ideas worked like a charm - I'll leave it up to the budget
Powers which one they'll want to use across the departments. Thanks all!