I need more than 7 IF conditions...

[Guest]

I am trying to create a formula which will calculate the amount of time spent
on a project given the conditions of a standard workday. Essentially this
just requires a conditional statement which takes into account a set lunch
time (12:00 -12:30) and break time (10:00 - 10:20). I have created a
formula to handle this over one workday, however when a project extends over
more than one day the possibilities are expanded. In such circumstances I
require more IF statements than excel will allow (7). Can anyone help with
this?

 

[Vitalie Ciobanu]

I had the same problem so It does interest me to!

 

[CaptainQuattro]

Would Boolean logic help?

For example:

Start time is in cell A2

If start time =10:00 include 40 minutes of worktime for that hour
If start time = 11:00 include 60 minutes

Formula is

(A2 = 10:00)*40+(A2 = 11:00) *60

Every true equation returns a value of 1
Every false equation returns a value of 0

You should be able to string as many of these together as you need.

 

[Bob Phillips]

There are many ways to achieve this, but essentially it depends upon the
data and the calculations, so it would help if you posted some example data,
what you need to do with it, and the formula that you have and need to
extend.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Redwynne]

I have the same problem. I want to flag school holidays from a list
extracted from our database. I have nearly 1300 records/dates and 18
date references (nine for each year and I have two years worth of
data). Here are the dates for the 2005 school holidays:

School Starts 26/01/2005
Term 1
Start 11/04/2005
End 25/04/2005
Term 2
Start 04/07/2005
End 15/07/2005
Term 3
Start 26/09/2005
End 07/10/2005
Term 4
Start 22/12/2005
End 27/01/2006

So far my IF statement looks like this but unfortunately I have two
years worth of school holidays (I have just listed one years for
reference):

=IF(B2<LookUps!$E$20,"Y",IF(B2<LookUps!$E$22,"N",IF(B2<LookUps!$E$23,"Y",IF(B2<LookUps!$E$25,"N",IF(B2<LookUps!$E$26,"Y",IF(B2<LookUps!$E$28,"N",IF(B2<LookUps!$E$29,"Y",IF(B2<LookUps!$E$31,"N",))))))))

The LookUps cell references in the above formula contain the school
holiday date. So this formula falls short due to the number of IF
statements one can have. Is there a reason why its limited to eight IF
statements?

If there is an easier way to achieve this please let me know. Thanks
for your help.

Helen

 

[Pete_UK]

There are different questions in this thread now, but to tackle yours,
Helen:

You could arrange your date table like this:

26/01/2005 N
11/04/2005 Y
25/04/2005 N
04/07/2005 Y
15/07/2005 N
26/09/2005 Y
07/10/2005 N
22/12/2005 Y
27/01/2006 N
and so on ...

Let's assume this occupies cells A1 to B9 of Sheet2.

Then your formula above could be replaced by:

=VLOOKUP(B2,Sheet2!$A$1:$B$9,2)

if B2 contains a date and you are trying to find out if that date
occurs in the school holidays - Y or N.

You can add more dates and just increase the range in the VLOOKUP
formula. You might like to check that the date does occur after the
earliest date in the table before calling the VLOOKUP formula, by means
of:

=IF(B2<Sheet2!$A$1,"Too early",VLOOKUP(B2,Sheet2!$A$1:$B$9,2))

The formula can be copied down to cover your 1300 dates.

Hope this helps.

Pete

 

[Redwynne]

Thanks for the info Pete. I appreciate the help

Unfortunately the formula isnt working for those dates before
26/1/2005. Gives me #N/A for all those dates 1/1/2005 to 25/1/2005
inclusive.

I have looked over my requirements and have realised that I actually
want included the end dates of each term as part of my "school holiday"
label so my IF statement should be like this:

=IF(B2<LookUps!$I$20,"Y",IF(B2<LookUps!$I$22,"N",IF(B2<=LookUps!$I$23,"Y",IF(B2<LookUps!$I$25,"N",IF(B2<=LookUps!$I$26,"Y",IF(B2<LookUps!$I$28,"N",IF(B2<=LookUps!$I$29,"Y",IF(B2<LookUps!$I$31,"N","Y"))))))))

with some <= instead of just <.

For example the school term that starts on 11/04/2005 and ends on
25/04/2005 should have "Y" labelled on all those dates that are between
(and inclusive) of these dates. The start and end school holiday dates
will be entered in by someone else so the spread sheet has to be self
sufficient and do things automatically.

I hope all this makes sense. BTW How does one get over the IF statement
restriction (8 arguments all up isnt it?) and *why *is there a limit of
how many you can have??

Thanks again
Helen

 

[Infinity]

Not sure if this is what you are asking for. Download attachment to see
if that is the formula that you want...




+-------------------------------------------------------------------+
|Filename: SchoolDates.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4553 |
+-------------------------------------------------------------------+

 

[Herman56]

I was stuck once more with a simular problem. In the past I wa
sometimes able to work around it, by creating one or more additiona
columns, which you always can hide for the users. But for my present
simular problem I had to create a help tabel. Some people like t
create this in the last columns of one of the present sheets, but
find it easier to create a new sheet. It can help you to avoid a lot o
problems...

And Helen: more then 7 IFs would make the system much slower. You
spreadsheet is getting much slower this way: you can check this b
copying your formulas down to the last row, and compare tha
spreadsheet with a simular one with help tabels, reducing IFs an
VLOOKUPs... Been there, done that...

Pau

 

[Pete_UK]

Helen,

You can extend the table with an earlier date so that you don't have
problems before 26/1/05. You can also add one day to the dates at the
end of the holidays, so your table becomes:

01/01/2005 Y
26/01/2005 N
11/04/2005 Y
26/04/2005 N
04/07/2005 Y
16/07/2005 N
26/09/2005 Y
08/10/2005 N
22/12/2005 Y
28/01/2006 N
and so on ...

The formula would now become:

=IF(B2<Sheet2!$A$1,"Too early",VLOOKUP(B2,Sheet2!$A$1:$B$10,2))

It does not take very long to set up the table of dates. Once they are
set up they do not need to change - you just need to ensure that your
formula includes the range of dates in your table. So, if your dates
are extended for another year so that the table occupied A1 to B20,
then the formula would become:

=IF(B2<Sheet2!$A$1,"Too early",VLOOKUP(B2,Sheet2!$A$1:$B$20,2))

Hope this helps.

Pete

 

[Pete_UK]

If you want to know how to get around the limit of 7 nested IF
statements, you might find some useful information at this link to Chip
Pearson's site:

http://www.cpearson.com/excel/nested.htm

Hope this helps.

Pete