Ignore any text in (complex) formula..

[sunil100]

Hi all,

I just created a fairly complex formula (well its not a basic SUM!)
everything works fine but i need the formula to ignore ANY text valu
(treating as a null entry?) and still to perform the calculation.

Here is the formula:

=IF(AND(B25<13/24,((C25-B25)*24)>7), ((C25-B25)*24)-1, ((C25-B25)*24))

It's a formula for a timesheet that i have created for my company. Th
majority of the values to be entered would be 'times' i.e 09.00, 17.15
etc but at times (in the same cell) text values such as OFF, A/L, B/
would need to be entered without the formula to result in #VALU
error.

Example Data:

A1 B1 C1 D1 E1 F1 G1

09.00 17.00 OFF OFF 10.00 14.00 (formula abv w/o no error)


This in theory sounds simply to me- just a case of how to ignore an
any text value- but im not an excel expert so any help would be muc
appreciated.

Thanks,

Suni

 

[jpendegraft]

This should work:

Use the following array formula

=SUM(IF(ISERROR(A1:G1),0,A1:G1))

Since this is an array formula, you must press Ctrl+Shift+Enter
rather than just Enter when you first enter the formula and
whenever you edit it later. If you do this properly, Excel will
display the formula enclosed in curly braces {}.


Cheers

 

[sunil100]

I thought of using what you said BUT i dont want the formula to resul
in 0 but just to ignore the text.

Example:

1, OFF, 5, 7, 2

when MY IF formula is applied to this it should result in the number 1
i.e 1+5+7+2 and hence IGNORING the text value 'OFF' yet still doing th
calculation.

N.B. Bare in mind that my formula is NOT a standard SUM formula (whic
ignores text automatically)

Thanks in advance

 

[Frank Kabel]

Hi
why not use
=SUM(A11)

SUM will ignore text values automatically

 

[Aladin Akyurek]

Do you mean something like:

=IF(N(B25)*N(C25),IF(AND(B25<13/24,((C25-B25)*24)>7), ((C25-B25)*24)-1,
((C25-B25)*24)),0)

 

[jpendegraft]

Your example and your formula don't match up, which makes it harder fo
me to provide a specific solution.

Without this, I will give you a generic recommendation:

I would code your equation as such:

Step through and evaluate whether or not the number "ISNUMBER", if i
is True then I would do the calculation and if it is false I would ski
it...working through each piece of the equation.

 

[sunil100]

Thanks for the replies everyone.

Yeah i know its difficult to understand without the excel sheet i
front of you. So i thought id attach it.

Just to briefly explain the file.

It's basically a weekly timesheet for employee's, where their hour
work per day are worked out and then the hours per week.

The complexity in the formula comes about from the fact that i
employees work more than 5 hours in a day AND start BEFORE 1pm then a
hour for lunch IS deducted ELSE its just the finish time minused by th
start time.

If you look at the excel sheet cell B6 contains the first instance o
the formula that calculates what ive described above:

=IF(AND(B5<13/24,((C5-B5)*24)>7), ((C5-B5)*24)-1, ((C5-B5)*24))


Also on the excel sheet i have typed in the text 'A/L' in cells D5 an
E5 which stands for 'annual leave'. As you can also see the formul
results in an error because of course its not a numerical value.

What I want is the formula to 'ignore' the text and still calculate th
totals.

I hope all this makes sense.

I've been trying to get this one thing to work for a long time now.

Any help in a solution would be overly appreciated.

Many thanks again,

Suni

 

[sunil100]

Not sure if I am attaching the file correctly...

Attachment filename: master_timesheet.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=659888

 

[Aladin Akyurek]

Did you try the suggestion I made?

 

[hgrove]

Aladin Akyurek wrote...

Do you mean something like:

=IF(N(B25)*N(C25),IF(AND(B25<13/24,((C25-B25)*24)>7),
((C25-B25)*24)-1,((C25-B25)*24)),0)

Two N calls not needed, and what happens if an employee ends work a
midnight? (The last likely isn't pertinent for the OP.)

=IF(COUNT(B25:C25)<2,0,
(C25-B25)*24-AND(B25<13/24,C25-B25>7/24)

 

[sunil100]

Hi Aladin Akyurek-

I am not worthy of your expertise!!!

That formula worked a treat

I know its all working- but could you just explain what the N is fo
and how it ignores the text values??

Sorry- i know i should be happy with what you have told me already an
i sure am but being a Network Analyst by trade, i would like to kno
the how's and why's of all problems


Once again many thanks to all who replied and utmost respect to Aladin
cheers mate.

Sunil