Rounding up with IF

  • Thread starter Thread starter paulab
  • Start date Start date
P

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???
 
paulab said:
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???
Click to expand...

=MAX(1000,SUM((J3="tabloid")*(D3*F3*2))+SUM((J3="letter")*(D3*F3)))
 
paulab said:
With help from the message board......
My formula: =SUM((J3="tabloid")*(D3*F3*2))+SUM((J3="letter")*(D3*F3))
Click to expand...

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?
Click to expand...

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))
 
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.
 
paulab said:
If J3 is neither tabloid or letter but left empty, I would like the result
to be 0 not 1000.
Click to expand...

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 -----
 
Back
Top