Removing #VALUE! where a blank is required, help please

[Jennasis]

Hi,

I need help to remove #VALUE! errors from the following formulas, which refer to a cell (Y) which also contains a formula. Basically, the three formulas wont read blank when Y is not displaying a value and I need them to be blank also.

I have looked into using the ISERROR function but no idea where to insert it so I failed there.

Help would be much appreciated! This is for a work project that is being implemented on Monday!

=IF(N4+O4+Y4+Z4=0,"",IF(Y4+Z4=0,"Open",IF(TODAY()-Y4>=55,"Closed",IF(Z4=0,"Open","Closed"))))

=IF(N4+O4+Y4+Z4=0,"",IF(O4+Y4+Z4=0,TODAY()-N4,IF(Y4+Z4=0,TODAY()-O4,IF(Z4="",Y4-O4,Z4-O4))))

=IF(N4+O4+Y4+Z4=0,"",IF(N4+Z4=0,TODAY()-O4,IF(N4=0,Z4-O4,IF(Y4+Z4=0,TODAY()-N4,IF(Z4=0,Y4-N4,Z4-N4)))))


Formula in Y is as follows:

=IF(S4+T4+U4+V4+W4+X4=0,"",IF(T4+U4+V4+W4+X4=0,S4,IF(U4+V4+W4+X4=0,T4,IF(V4+W4+X4=0,U4,IF(W4+X4=0,V4,IF(X4=0,W4,X4))))))

Many Thanks!

Jenn

[shg]

Welcome to the forum.

Please take a few minutes to read the forum rules, and then amend your thread title accordingly.

Thanks.

[Jennasis]

Welcome to the forum.

Please take a few minutes to read the forum rules, and then amend your thread title accordingly.

Thanks.

Apologies; updated with more precise title

[ChemistB]

The value of "" is called a null string (empty text string). You cannot add or subtract that from a number. For addition, you can get around that by using the SUM function (which will ignore null strings). ie. =SUM(N4,O4,Y4,Z4).

Is it only your Y column that's giving null strings? You could modify your formulas to give 0 instead (and you can format the cells not to show zeros) i.e.

=IF(S4+T4+U4+V4+W4+X4=0,0,IF(T4+U4+V4+W4+X4=0,S4,IF(U4+V4+W4+X4=0,T4,IF(V4+W4+X4=0,U4,IF(W4+X4=0,V4 ,IF(X4=0,W4,X4))))))

[Jennasis]

The value of "" is called a null string (empty text string). You cannot add or subtract that from a number. For addition, you can get around that by using the SUM function (which will ignore null strings). ie. =SUM(N4,O4,Y4,Z4).

Is it only your Y column that's giving null strings? You could modify your formulas to give 0 instead (and you can format the cells not to show zeros) i.e.

=IF(S4+T4+U4+V4+W4+X4=0,0,IF(T4+U4+V4+W4+X4=0,S4,IF(U4+V4+W4+X4=0,T4,IF(V4+W4+X4=0,U4,IF(W4+X4=0,V4 ,IF(X4=0,W4,X4))))))

Thanks for responding

Y needs to read as null when nothing is entered as it is a date and if there is no data in that row it needs to stay blank for reporting purposes, as do the other 3 that are currently showing #VALUE! . For that reason I have to use null instead of 0 there.

The two formulae after the first "Open Closed" formula calculate the number of days that an item has been open and all the cells calculated from are dates.

Thanks for your help

[ChemistB]

You can convert "" into a number (0) in your formulas by using N(Y4)

So
=IF(N4+O4+Y4+Z4=0,"",IF(Y4+Z4=0,"Open",IF(TODAY()-Y4>=55,"Closed",IF(Z4=0,"Open","Closed"))))
becomes
=IF(N4+O4+N(Y4)+Z4=0,"",IF(N(Y4)+Z4=0,"Open",IF(TODAY()-N(Y4)>=55,"Closed",IF(Z4=0,"Open","Closed"))))
Does that work for you?

[Jennasis]

You can convert "" into a number (0) in your formulas by using N(Y4)

So
=IF(N4+O4+Y4+Z4=0,"",IF(Y4+Z4=0,"Open",IF(TODAY()-Y4>=55,"Closed",IF(Z4=0,"Open","Closed"))))
becomes
=IF(N4+O4+N(Y4)+Z4=0,"",IF(N(Y4)+Z4=0,"Open",IF(TODAY()-N(Y4)>=55,"Closed",IF(Z4=0,"Open","Closed"))))
Does that work for you?

Thanks, will give that a go! Wish I could take my work home to test but will have to wait til Monday ><,

Fingers crossed!