Round in an IF statement

H

heater

Where does the Round go in the following formula:

=IF($C$19<5000000,500000,IF($C$19>5000000,(D52/$D$95)*$C$19))

I want it to round to the nearest 1,000.
 
H

heater

It actually is

=IF($C$19<5000000,500000,IF($C$19>5000000,ROUND((D52/$D$95)*$C$19,-3)))

Thanks for the direction.
 
P

Peter Aitken

heater said:
Where does the Round go in the following formula:

=IF($C$19<5000000,500000,IF($C$19>5000000,(D52/$D$95)*$C$19))

I want it to round to the nearest 1,000.
Click to expand...

Divide by 1000, round, then multiply by 1000.
 
J

joeu2004

heater said:
Where does the Round go in the following formula:
=IF($C$19<5000000,500000,IF($C$19>5000000,(D52/$D$95)*$C$19))
I want it to round to the nearest 1,000.
Click to expand...

You got your answer, and there is nothing wrong with
it. But I'm curious .... Why not simply round the IF()
result? That is:

= ROUND(IF(..., ..., ...), -3)

Probably easier for someone else to read. It does not
matter that 500000 is already rounded to the desired
degree.

The only time that would not work for you is if you
do not want the result of one of the clauses to be
rounded. I think that would be unusual. For example:

=IF(..., 543210, ROUND(..., -3))

PS: Are you sure you wrote the nested IF() functions
as you intended for your purposes? They result in the
word "FALSE" when $C$19 is 500000, but it results in
500000 when $C$19 is less than 500000. Seems a bit odd.
 
J

Jerry W. Lewis

The formula as posted would return FALSE instead of a number if
$C$19=5000000. Putting ROUND() outside the IF() would coerce that FALSE
to a zero, making it harder to figure out where the problem was coming from.

Jerry
 
Top