Rounding up to a multiple of 12

[pettyc]

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-P5>0,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

 

[David Biddulph]

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-P5>0,CEILING((E5+H5+K5+L5-F5-M5-P5+1)/2,12),"")

 

[ShaneDevenshire]

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