Need help with a formula

[Guest]

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 Govier]

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

 

[RagDyeR]

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!
=====================================================


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

 

[Bernie Deitrick]

Roger,

Instead of

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

you could use

ROUNDUP(2080*G2,-3)

HTH,
Bernie
MS Excel MVP

 

[Roger Govier]

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!!