Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rounding up to a multiple of 12
I want to add several columns, subtract several columns and if the result is
gt zero I want to add 1 to it, divide by 2 and then round up to a multiple of 12. If the result is 0 I want the result to be a space. I know about the ceiling statement (sort of) but Can I do this in one statement somehow? =IF(E5+H5+K5+L5-F5-M5-P50,SUM(E5+H5+K5+L5-F5-M5-P5+1)/2,"") In a different column I can insert =CEILING(I5,12) - but if the result is 0 it displays 0 instead of a space |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rounding up to a multiple of 12
1 You could have said =IF(I5="","",CEILING(I5,12))
2 You don't need the SUM function in SUM(E5+H5+K5+L5-F5-M5-P5+1); (E5+H5+K5+L5-F5-M5-P5+1) will do 3 If you want to combine your 2 formulae you could use =IF(E5+H5+K5+L5-F5-M5-P50,CEILING((E5+H5+K5+L5-F5-M5-P5+1)/2,12),"") -- David Biddulph "pettyc" wrote in message ... I want to add several columns, subtract several columns and if the result is gt zero I want to add 1 to it, divide by 2 and then round up to a multiple of 12. If the result is 0 I want the result to be a space. I know about the ceiling statement (sort of) but Can I do this in one statement somehow? =IF(E5+H5+K5+L5-F5-M5-P50,SUM(E5+H5+K5+L5-F5-M5-P5+1)/2,"") In a different column I can insert =CEILING(I5,12) - but if the result is 0 it displays 0 instead of a space |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rounding up to a multiple of 12
Hi,
Not only do you not need SUM as David said, but in fact you should not use it. Here's what you are asking Excel to do: Add and Subtract a bunch of number and when you get the result, say 10, then SUM(10). But 10 is already a single number so you are asking Excel to SUM(10) which is 10. These kind of formulas use computer power and make the file larger because the formulas are longer. Not that those factors are of any consequence with a single formula, but when there are thousands or millions of these in a spreadsheet, they start have an impact. Also, if you put the (E5+H5+K5+L5-F5-M5-P5+1) portion of the formula into a cell like I5 then you can drop the parenthesis: =E5+H5+K5+L5-F5-M5-P5+1 -- Thanks, Shane Devenshire "David Biddulph" wrote: 1 You could have said =IF(I5="","",CEILING(I5,12)) 2 You don't need the SUM function in SUM(E5+H5+K5+L5-F5-M5-P5+1); (E5+H5+K5+L5-F5-M5-P5+1) will do 3 If you want to combine your 2 formulae you could use =IF(E5+H5+K5+L5-F5-M5-P50,CEILING((E5+H5+K5+L5-F5-M5-P5+1)/2,12),"") -- David Biddulph "pettyc" wrote in message ... I want to add several columns, subtract several columns and if the result is gt zero I want to add 1 to it, divide by 2 and then round up to a multiple of 12. If the result is 0 I want the result to be a space. I know about the ceiling statement (sort of) but Can I do this in one statement somehow? =IF(E5+H5+K5+L5-F5-M5-P50,SUM(E5+H5+K5+L5-F5-M5-P5+1)/2,"") In a different column I can insert =CEILING(I5,12) - but if the result is 0 it displays 0 instead of a space |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I need a formula with rounding up & rounding down to the nearest . | Excel Worksheet Functions | |||
Need help in rounding answers to nearest multiple | Excel Worksheet Functions | |||
Formula for rounding a column of currency to a multiple | Excel Worksheet Functions | |||
Excel Rounding Up to Nearest Multiple | Excel Worksheet Functions | |||
Rounding a number to a multiple quantity that adds to a fixed total number | Excel Worksheet Functions |