isblank

[Stclements1]

I am trying to leave a cell empty if the correspondending cell has no date in it.
In column B cells b9:b44 there are dates. These dates are based on the value in cell b3. If the value in cell b3 is 1 column b will show 12 dates (b9:b20) if the cell value is 2 it will show 24 dates (b9:b32) and if its 3 it will show 36 dates (b9:b44).
If there is a date in say cell b9 then this calculation (B2*(B4*12)/365)*(B9-B1) is done in cell c9 and so on b10 to c10 etc.
If there is no date in the b cell I would like the corresponding c cell to remain empty but because I have dragged the formula through cells c9:c44 if the value in cell b3 is 1 dates will show in cells b9:b20 (represents 12 months) and the remaining b cells in the array b9:b44 will be empty but in the corresponding c cells it returns the word "VALUE" . Is there a way that I can maybe add the isblank formula to my existing (B2*(B4*12)/365)*(B9-B1) formula in the c column so that if there is no date in the b cell the corresponding c cell remains empty. I did try it and it returned SPILL so clearly I was doing something incorrect.

 

[jasonb75]

If it shows #VALUE! then you only need to trap the error,

=IFERROR((B2*(B4*12)/365)*(B9-B1),"")

#SPILL is an excel 365 error, if that is what you're now using please update your profile to show this.

 

[Stclements1]

Many thanks for that it has worked perfectly and as mentioned I have now updated profile. Although whilst the calculation in column c leaves an empty cell where appropriate I have the formula in column E cells E9:E44 $B$2/$C$3 which again I only want the calculation to be done if there is a value in the corresponding D cell so I tried in cell e33 IFERROR(IF(D33>1;$B$2/$C$3);"") and whilst there was no value in cell d33 e33 still did the b2/c2 calculation and returned a value. I also tried with just the IF statement and that to returned a value in E33

 

[jasonb75]

There is a difference between blank and empty. The error method worked on your first formula because you were trying to perform a mathematical operation on a blank cell.

An empty cell has a numeric value of 0, a blank cell evaluates as text, which is greater than any number.

=IF(D33="";"";$B$2/$C$3)

Looks like you forgot to save when you updated your profile, it still shows 2016 at the moment.

 

[Stclements1]

Perfect and many thanks. Have updated profile again and saved.