Round in an IF statement

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.

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

www.OfficeArticles.com


"heater" <heater@discussions.microsoft.com> wrote in message
news:92C2F692-9C0A-4CF6-A8B2-C496CFCF1F9C@microsoft.com...
> 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.

Ooops!!
=IF($C$19<5000000,500000,IF($C$19>5000000,round((D52/$D$95)*$C$19),-3)))
*******************
~Anne Troy

www.OfficeArticles.com


"heater" <heater@discussions.microsoft.com> wrote in message
news:92C2F692-9C0A-4CF6-A8B2-C496CFCF1F9C@microsoft.com...
> 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.

It actually is

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

Thanks for the direction.

"Anne Troy" wrote:

> Ooops!!
> =IF($C$19<5000000,500000,IF($C$19>5000000,round((D52/$D$95)*$C$19),-3)))
> *******************
> ~Anne Troy
>
> www.OfficeArticles.com
>
>
> "heater" <heater@discussions.microsoft.com> wrote in message
> news:92C2F692-9C0A-4CF6-A8B2-C496CFCF1F9C@microsoft.com...
> > 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.
>
>
>

"heater" <heater@discussions.microsoft.com> wrote in message
news:92C2F692-9C0A-4CF6-A8B2-C496CFCF1F9C@microsoft.com...
> 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.

Divide by 1000, round, then multiply by 1000.


--
Peter Aitken

Remove the crap from my email address before using.

heater wrote:
> 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.

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.

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

joeu2004@hotmail.com wrote:

> heater wrote:
>
>>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.
>>
>
> 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.