Excel 2013: getting rid of a #value with isnumber, iserror, isblank 1

[pendle666]

Hello

Cell G25 of my spreadsheet has a #value message in it.

The formula in G25 is

=IF(ISERROR(L23)," ",TEXT(L24+29,"mmmm")&" and "&TEXT(L23,"mmmm"))

Cell L23 referred to here also contains a formula. I've used ISERROR, ISNUMBER OR ISBLANK in various other cells in this spreadsheet and it's made the #value or #name go away. I want this to happen because the form I'm doing will be used by technophobes and even though #value is perfectly valid while there is no data there, these two dinosaurs are insistent there's a problem with it overall and are refusing to use it.

I'm thinking that because there is TEXT which is going into G25 my usual formula structure isn't working.

For reference cell L23 referred to contains:
=IF(ISNUMBER(L22),IF(DAY(L22)=15,L22,IF(DAY(L22)>15,DATE(YEAR(L22),MONTH(L22),15),EDATE(DATE(YEAR(L22),MONTH(L22),15),-1))),"")

And L24 contains
=IF(ISNUMBER(L23),IF(ABS(DAY(L23-8*7+1)-15)>=EOMONTH(L23-8*7+1,0)-L23-8*7+1+15,DATE(YEAR(L23-8*7+1),MONTH(L23-8*7+1),16),DATE(YEAR(L23-8*7+1),MONTH(L23-8*7+1)+1,16)),"")


pendle


thank you for helping

____________
Pendle

 

[SkipVought]

hi,

If you're using Excel version 2007+, consider using the IFERROR() function, rather than an IF() as you are using.

 

[pendle666]

Hi

Using Excel 2013.

When I changed G23 to

=IFERROR(ISERROR(L23)," ",TEXT(L24+29,"mmmm")&" and "&TEXT(L23,"mmmm"))

it says that there are too many arguments for the function. Using the Insert Function wizard, it doesn't like it when it gets to

TEXT(L24+29,"mmmm")&" and "&TEXT(L23,"mmmm"))

But that is what's needed. For example, L23 contains 15/01/2015 and L24 contains 16/11/2014. G23 then needs to read December and January. (It adds on 29 days to L24 to find the next payday).


Pendle



thank you for helping

____________
Pendle

 

[SkipVought]

What if you had a cell somewhere that did the TEXT() and concatenation? Then you only have that cell reference in your expression.

 

[pendle666]

Excellent thank you.

thank you for helping

____________
Pendle