Using Roundup in an IF Statement

[LBW]

This is probably simple but I can't get it to work. I need to have
the number in D10, if is less than 50000, rounded up to the nearest
hundred. Then I need to apply the formula (((D10/1000)*0.23)*12) to
the rounded up number. If the number in D10 is 50,000 or more, the
formula should return a hard number, in this case it will be 138.

=IF((D10*1)<50000, roundup ((d10*1),2), (((D10/1000)*0.23)*12),138)

 

[Lars-Åke Aspelin]

This is probably simple but I can't get it to work. I need to have
the number in D10, if is less than 50000, rounded up to the nearest
hundred. Then I need to apply the formula (((D10/1000)*0.23)*12) to
the rounded up number. If the number in D10 is 50,000 or more, the
formula should return a hard number, in this case it will be 138.

=IF((D10*1)<50000, roundup ((d10*1),2), (((D10/1000)*0.23)*12),138)

Try this formula:

=IF(D10<50000,ROUNDUP(D10,-2)*0.00276,138)

Hope this helps / Lars-Åke

 

[Joe User]

rounded up to the nearest hundred.
[....]roundup ((d10*1),2)

Your ROUNDUP usage rounds up to the nearest hundredth (0.01), not the
nearest hundred (100). Which do you want?

As always, a numerical example would have been helpful to resolve
ambiguities and mistakes in the English description.

Noting that 50*0.23*12 is 138, I suspect the following is what you want to
compute:

=MIN(138, 0.23*12*ROUNDUP(D10,-2)/1000)

But if you want the result to be dollars or dollars and cents, use one of
the following:

dollars: =MIN(138, ROUND(0.23*12*ROUNDUP(D10,-2)/1000, 0))

cents: =MIN(138, ROUND(0.23*12*ROUNDUP(D10,-2)/1000, 2))

Change D10 to D10*1 or --D10 only if D10 is text (TYPE(D10)=2), not numeric
(TYPE(D10)=1). But why is D10 text?


----- original message -----