Using Roundup in an IF Statement
 

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)

Using Roundup in an IF Statement
 

On Tue, 8 Dec 2009 11:14:33 -0800 (PST), LBW
wrote:

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

Using Roundup in an IF Statement
 

"LBW" wrote:
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 -----

"LBW" wrote in message
...
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)