Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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??? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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))) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rounding up with IF
=MAX(1000,SUM((J3="tabloid")*(D3*F3*2))+SUM((J3="l etter")*(D3*F3)))
Regards, Bernd |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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??? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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??? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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)) |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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)) . |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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)) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ROUNDING FORMULA =ROUND(B12/$B$10,1) ROUNDING TO HIGH | Excel Worksheet Functions | |||
IF take 2 (ROUNDING) | Excel Discussion (Misc queries) | |||
I need a formula with rounding up & rounding down to the nearest . | Excel Worksheet Functions | |||
Rounding | Excel Discussion (Misc queries) | |||
rounding help | Excel Discussion (Misc queries) |