Formula to Ignore Values (Not a =SUM formula)

Hi Microsoft chaps & Excel Guru's, a point to ponder over the weekend,

I am currently working on a 3 shift rotation schedule for ($$) for a local company when I came across a problem that so far I have not been able to resolve without using a work around.

I have a few formulas to perform a calculation from shift start & end times but the data supplied also includes text values such as "Off Sick, Vacation, etc, etc" and as you may know Excel returns #Value if a formula finds text within the calculation.

After doing some research on-line no one seems to have an answer or a UDF that resolves this issue that doesn't include some kind of SUM formula, I know SUM formulas work but I don't need SUM or something like SUMPRODUCT (--( etc, yet, that will come later.

My current solution has been to use a helper cell to define whether the cells in question are text or numbers by using a modified formula to include using that cell: (see attachment)

=IF(ISNUMBER(L$9),IF(L9>0.1,IF(L8>L7,IF(MOD(L8+1/12,1)<=1/3,0,MIN(0.5833,L8)-IF(L8<=0.25,L7,MAX(L7,0.25))),MAX(0,0.5833-MAX(0.25,L7))+MAX(0,L8-0.25))*24,0))

I can get this to work just fine but it also returns "FALSE" if there is text which I think is a bit messy and forces another work around solution.

So my point is this, and this is for you Guru's:

a) Why doesn't Microsoft create a function that does work ? - something like IGNORE-BLINDALLEYS-TEXT-VALUES() - because numbers and text ARE ubiquitous in Excel !!

or

b) Am I missing something in my formula ?

or

c) Does anyone know of a solution that doesn't include a helper cell or know of a UDF that works.

or

d) Should I just close my workbook and help the wife dig out the 3 million Christmas Decorations we seem to have

Rant over

The attachment shows what and where the issue is.

Have you considered using the IFERROR function?

=IFERROR(your-formula,"")

L9=L8-L7
becomes...
=IFERROR(SUM(L8,-L7),"")
copied across

Then P10 (and the others in that row) become...
=IF(P9="","",IF(P9>0.1,IF(P8>P7,IF(MOD(P8+1/12,1)<=1/3,0,MIN(0.5833,P8)-IF(P8<=0.25,P7,MAX(P7,0.25))),MAX(0,0.5833-MAX(0.25,P7))+MAX(0,P8-0.25))*24,0))
(sure there is a simpler way to do that, too)

Originally Posted by BlindAlley

=IF(ISNUMBER(L$9),IF(L9>0.1,IF(L8>L7,IF(MOD(L8+1/12,1)<=1/3,0,MIN(0.5833,L8)-IF(L8<=0.25,L7,MAX(L7,0.25))),MAX(0,0.5833-MAX(0.25,L7))+MAX(0,L8-0.25))*24,0))

Hi,

I think you have missed the Value_if_False in your first IF:

P10
=IF(ISNUMBER(P$9),IF(P9>0.1,IF(P8>P7,IF(MOD(P8+1/12,1)<=1/3,0,MIN(0.5833,P8)-IF(P8<=0.25,P7,MAX(P7,0.25))),MAX(0,0.5833-MAX(0.25,P7))+MAX(0,P8-0.25))*24,0),"abcd")


SUM Function ignores text values, so this in P9:
=SUM(P8)-SUM(P7)

Regards,

Originally Posted by Khalidngo

=SUM(P8)-SUM(P7)

That can all be done in 1 SUM...
SUM(L8,-L7)

Thanks Ford, didn't saw your post.

Originally Posted by Khalidngo

Thanks Ford, didn't saw your post.

Not a problem, and thanks for the feedback

Originally Posted by Khalidngo

Thanks Ford, didn't saw your post.

Not a problem, and thanks for the feedback

Happy to help

If this answered your question, please take a moment to mark the thread as "solved" - it helps keep things neat and tidy lol, and consider adding the reputation to those that helped (see points 2 & 3 below my 1st post to you)