Rounding up with IF
 

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???

Rounding up with IF
 

paulab wrote:
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="l etter")*(D3*F3)))

Rounding up with IF
 

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

Regards,
Bernd

Rounding up with IF
 

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

"paulab" wrote:

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???

Rounding up with IF
 

Go on! Humour us, Paula! Tell us what you want the SUM function to do in
that formula?
You've calculated ((J3="tabloid")*(D3*F3*2)) ; what did you intend the SUM
function to add to that?
And then you've calculated ((J3="letter")*(D3*F3)) ; what did you intend the
SUM function to add to that result?
If you don't understand what the SUM function does, please look it up in
Excel help.
--
David Biddulph

"paulab" wrote in message
...
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???

Rounding up with IF
 

"paulab" wrote:
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))

Rounding up with IF
 

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

"paulab" wrote:
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))

.

Rounding up with IF
 

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

"paulab" wrote in message
...
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" wrote:

"paulab" wrote:
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))