Complex rounding in a formula

[heytherejem]

So I have to work out how long a customer is with us till they cancel a policy by using a combination of information, including policy status, start date and cancellation date.

I've used a formula
=IFERROR(ROUND(SUM((H2-B2)/30),0),"Live")

This takes the cancelled date from the start date to give the total number of dates the policy was live, divides by 30 to give an approximate month, and rounds it to a whole number. The iferror just shows that the policy hasn't cancelled.

The thing is - if the number of days the policy was live is more than 14 - this is 0.4666667 when divided by 30 - so it would round to 0. We want to round anything greater than 0.466667 to 1 so we count this as a cancellation. I doubt I can do this in the same formula as above - I probably need to do something in a new column - but I have no idea.

I have attached some sample data which has my workings.

Anyone super clever - can you help?

[Special-K]

This doesnt solve your problem but makes the calculation look better.

Remove the SUM from the formula
You only have one parameter that's H2-B2
So you're SUMming (H2-B2) with...absolutely nothing.
That's like saying SUM(3), no point. It's just 3.

[Special-K]

Does this work?

in I2
=IFERROR(IF(H2-B2<=14,1,ROUND((H2-B2)/30,0)),"Live")

[samba_ravi]

try
I2=IFERROR(ROUND((H2-B2)/30+0.033334,0),"Live")
OR
=IFERROR(ROUND((H2-B2+1)/30,0),"Live")