Need help with a formula
 

Hi - I'm pretty much an excel newbie, and I need a little help with a
formula. I need to take the annual salary, which is the (comp rate * 2080),
round it up to the nearest 1000, multiply it by the salary factor, then if it
is over 89,000, round it down to 89,000. That last part is what is eluding
me, is there any way to do it? I'm also not quite sure how to do the
rounding, though I imagine I can figure that out. Thanks in advance for any
help.

G H I
Comp Rate Annual Salary Salary Factor
$27.80 $57,824.00 1.0
$17.96 $37,356.80 1.0
$21.78 $45,302.40 0.5
$15.89 $33,051.20 1.0

One way
=MIN(89000,ROUNDUP(2080*G2/1000,0)*1000*I2)

--
Regards
Roger Govier
"Terrell_Z" wrote in message
...
Hi - I'm pretty much an excel newbie, and I need a little help with a
formula. I need to take the annual salary, which is the (comp rate *
2080),
round it up to the nearest 1000, multiply it by the salary factor, then if
it
is over 89,000, round it down to 89,000. That last part is what is eluding
me, is there any way to do it? I'm also not quite sure how to do the
rounding, though I imagine I can figure that out. Thanks in advance for
any
help.

G H I
Comp Rate Annual Salary Salary Factor
$27.80 $57,824.00 1.0
$17.96 $37,356.80 1.0
$21.78 $45,302.40 0.5
$15.89 $33,051.20 1.0

Try this in J2:

=MIN(ROUND(H2/1000,0)*1000*I2,89000)

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"Terrell_Z" wrote in message
...
Hi - I'm pretty much an excel newbie, and I need a little help with a
formula. I need to take the annual salary, which is the (comp rate * 2080),
round it up to the nearest 1000, multiply it by the salary factor, then if
it
is over 89,000, round it down to 89,000. That last part is what is eluding
me, is there any way to do it? I'm also not quite sure how to do the
rounding, though I imagine I can figure that out. Thanks in advance for any
help.

G H I
Comp Rate Annual Salary Salary Factor
$27.80 $57,824.00 1.0
$17.96 $37,356.80 1.0
$21.78 $45,302.40 0.5
$15.89 $33,051.20 1.0

Roger,

Instead of

ROUNDUP(2080*G2/1000,0)*1000

you could use

ROUNDUP(2080*G2,-3)

HTH,
Bernie
MS Excel MVP


"Roger Govier" wrote in message
...
One way
=MIN(89000,ROUNDUP(2080*G2/1000,0)*1000*I2)

--
Regards
Roger Govier
"Terrell_Z" wrote in message
...
Hi - I'm pretty much an excel newbie, and I need a little help with a
formula. I need to take the annual salary, which is the (comp rate * 2080),
round it up to the nearest 1000, multiply it by the salary factor, then if it
is over 89,000, round it down to 89,000. That last part is what is eluding
me, is there any way to do it? I'm also not quite sure how to do the
rounding, though I imagine I can figure that out. Thanks in advance for any
help.

G H I
Comp Rate Annual Salary Salary Factor
$27.80 $57,824.00 1.0
$17.96 $37,356.80 1.0
$21.78 $45,302.40 0.5
$15.89 $33,051.20 1.0

Hi Bernie

Thank you for pointing this out. Much neater.
Even though I have had this pointed out to me before, I somehow always
manage to forget. I guess it must be that it seems counter intuitive to me
to round to a negative number!!

--
Regards
Roger Govier
"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Roger,

Instead of

ROUNDUP(2080*G2/1000,0)*1000

you could use

ROUNDUP(2080*G2,-3)

HTH,
Bernie
MS Excel MVP


"Roger Govier" wrote in message
...
One way
=MIN(89000,ROUNDUP(2080*G2/1000,0)*1000*I2)

--
Regards
Roger Govier
"Terrell_Z" wrote in message
...
Hi - I'm pretty much an excel newbie, and I need a little help with a
formula. I need to take the annual salary, which is the (comp rate *
2080),
round it up to the nearest 1000, multiply it by the salary factor, then
if it
is over 89,000, round it down to 89,000. That last part is what is
eluding
me, is there any way to do it? I'm also not quite sure how to do the
rounding, though I imagine I can figure that out. Thanks in advance for
any
help.

G H I
Comp Rate Annual Salary Salary Factor
$27.80 $57,824.00 1.0
$17.96 $37,356.80 1.0
$21.78 $45,302.40 0.5
$15.89 $33,051.20 1.0