Rounding up with IF

[paulab]

Hello,
With help from the message board......
My formula: =SUM((J3="tabloid")*(D3*F3*2))+SUM((J3="letter")*(D3*F3))
Now I would like the result to round up to 1000 ONLY if less than 1000.
Would like the result to stay what it is if over 1000.
How would I accomplish this???

 

[Glenn]

Hello,
With help from the message board......
My formula: =SUM((J3="tabloid")*(D3*F3*2))+SUM((J3="letter")*(D3*F3))
Now I would like the result to round up to 1000 ONLY if less than 1000.
Would like the result to stay what it is if over 1000.
How would I accomplish this???

=MAX(1000,SUM((J3="tabloid")*(D3*F3*2))+SUM((J3="letter")*(D3*F3)))

 

[Bernd P]

=MAX(1000,SUM((J3="tabloid")*(D3*F3*2))+SUM((J3="letter")*(D3*F3)))

Regards,
Bernd

 

[Sean Timmons]

=MAX(SUM((J3="tabloid")*(D3*F3*2))+SUM((J3="letter")*(D3*F3)),1000)

 

[Joe User]

With help from the message board......
My formula: =SUM((J3="tabloid")*(D3*F3*2))+SUM((J3="letter")*(D3*F3))

I don't believe SUM serves any useful purpose in this context.

Now I would like the result to round up to 1000 ONLY if less than 1000.
Would like the result to stay what it is if over 1000.
How would I accomplish this?

Ostensibly:

=MAX(1000, (J3="tabloid")*D3*F3*2 + (J3="letter")*D3*F3)

But note:

1. (J3="tabloid")*D3*F3*2 + (J3="letter")*D3*F3 results in zero if J3 is
neither "tabloid" nor "letter". Is that what you want? It's no problem if
J3 can be only "tabloid" or "letter".

2. It might be clearer and more flexible for any future changes if you
wrote:

=MAX(1000, IF(J3="tabloid", D3*F3*2, IF(J3="letter", D3*F3, 0)))

Or J3 can be only "tabloid" or "letter" (i.e. not "tabloid"):

=MAX(1000, IF(J3="tabloid", D3*F3*2, D3*F3))

 

[paulab]

Thank you,
All your points are well taken.
If J3 is neither tabloid or letter but left empty, I would like the result
to be 0 not 1000. I have not been able to accomplish this either. Can you
help me again.

 

[Joe User]

If J3 is neither tabloid or letter but left empty, I would like the result
to be 0 not 1000.

There are many ways to write that. One simple way:

=IF(J3="tabloid", MAX(1000,D3*F3*2), IF(J3="letter", MAX(1000,D3*F3), 0))


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