Excel 2013: removing #value error in a total when there is nothing in the cells to add up 2

[pendle666]

Hello

I've got a small table:

[pre]
A B C D E
1 01/01/14 20/03/14 =IF(A1=0," ",(B1-A1+1)) 0.00 =D1/365*C1

[/pre]
C1 has a formula to count the number of days between A1 and B1. I've used the IF statement so that for any of the rows that are empty there is nothing in the field - it leaves a trailing "1" otherwise.

Now if the line is blank, I'm getting #value in column E which is quite right. However, this table will have several lines, but not necessarily with any data in it. This table is actually used to calculate pensions, so it depends on the number of years the person has been with us as to how many lines will have any entries.

Is there any way I can make column E be blank until something gets entered?



thank you for helping

____________
Pendle

 

[SkipVought]

Hi,

In column E

=if(C1=" ",0,...)

If your default value then ZERO else your formula

 

[1726]

There are many approaches you can take to this problem.

In your case you can use a similar construct to the one you provide in column C

=IF(A1=0,"",D1/365+C1)

Notice "" in place of your " "

" " puts a string value into the cell which gives rise to your #VALUE error
"" keeps the cell empty which is treated as a zero value by other formulas - however this in itself will not resolve your problem as dividing by zero will give you a #DIV/0 error instead!

a more general solution would be:

=IF(ISERROR(D1/365+C1),"",D1/365+C1)

i.e. test calculation for an error , if there is leave the cell blank and if there isn't do it!

 

[1726]

Oops
Sorry for the typo in previous reply

try:

=IF(A1=0,"",D1/365*C1)

and

=IF(ISERROR(D1/365*C1),"",D1/365*C1)

 

[SkipVought]

There's also the Excel 2007+ fubpnction IFERROR()

 

[pendle666]

Excellent! Thank you all for your speedy replies and making me look good at work!

thank you for helping

____________
Pendle