?
Solved

Using multiple"if" "ands'

Posted on 2014-09-22
14
Medium Priority
?
120 Views
Last Modified: 2014-10-30
Golf tee times.
I have a column "I"  that has day name in it  i.e.  Mon thru Sun.
I have other column "J"that has time in it  0700-1900

 I want to show a return value of  "T1", "T2", "T3", "T4", "T5"
i.e  if ($I14="FRI"  and  $J14>TIME(10,50,0) then  $K14="T1" else "T2")

I need to do this for all seven days.  some days have 3 time conditions and 3 'return values.

if ($I14="SUN"  and  $J14>TIME(10,50,0)<TIME(14,30,0) then  $K14="T3" else "T2")

Above probably does not explain well:
 7 days
5 Time values
T1-T5 represent values.

If a golfer plays before 1050 on a Saturday that would need to show T1
If a golfer plays after 1051 and before 1700 on a Saturday that would need to show T2
If a golfer plays after 1700 on a Saturday that would need to show T3
So I need to determine the "DAY" from column I  and then do the if  for each day.
Trying to use one formula for all seven days.

I have wrestled with this for about a month, I can not get it to work for all seven days.

Any direction is appreciated....
0
Comment
Question by:tomrector
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 2
  • 2
14 Comments
 
LVL 1

Expert Comment

by:Richard Francis
ID: 40337667
Hi Tom,

Out of curiosity, what do you use T4 and T5 for?

Richard.
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40338023
^with Richard.  I presume you have T4 and T5 assigned for weekday and/or Sunday timeframes.
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40338072
You don't need multiple IF statements to accomplish this, but rather a lookup matrix that you can derive the proper T-value from any given day and tee time.  Once you have that, you can use INDEX and MATCH functions to return the correct value in the table.

For example, create a table with the day abbreviations in the left column and all the key time values in the top row.
 example matrixUnfortunately, your example question only provides for us the T-values for Saturday at 10:50 and 17:00, but I've added a few others for this demonstration.  NOTE:  you only need enough columns for all the possible breakpoints in the time, plus your start and end times.  These are currently the values in bold on top with light red backgrounds.  If your other days use the same break points, then none of the other columns would be necessary.

I added this table to "Sheet2".  In "Sheet1" I added a column of days (column I) and tee times (column J).  For this demonstration, I'm showing all tee times from 7:00 to 19:00 in ten-minute increments.

To the right, I've added a column (K) that locates the row where the day appears in the matrix on Sheet2; another column (L) that locates the column where the time falls in the range of time values in the matrix on Sheet2 (this is a sorted lookup; it's looking for the value less than one in the range); and a final column (M) that uses the INDEX function to locate the value at the intersection of the row and column values found in K and L.

For example: If the day is SAT and the tee time is 11:00, then the day is on row 7, the time closest match is in column 4 and the result is "T2":
example lookup
All of these formulas can be combined into one (seen in column N):
=INDEX(Sheet2!$A$1:$J$8,MATCH(I2,Sheet2!$A$2:$A$8,0)+1,MATCH(J2,Sheet2!$B$1:$J$1,1)+1)
eliminating the need for the previous three.  But I am showing those also for reference.

Example file attached.
-Glenn
EE-TValues.xlsx
0
WordPress Tutorial 1: Installation & Setup

WordPress is a very popular option for running your web site and can be used to get your content online quickly for the world to see. This guide will walk you through installing the WordPress server software and the initial setup process.

 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40338084
PS  A variation of the above formula that is a little simpler:
=INDEX(Sheet2!$B$2:$J$8,MATCH(I2,Sheet2!$A$2:$A$8,0),MATCH(J2,Sheet2!$B$1:$J$1,1))

(Changes the INDEX array and removes the +1 added to the row and column arguments.)
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40344549
Did you have any questions about the possible solutions I offered?  Let us know and I'm sure we can help resolve it.

Again, information on the T4 and T5 categories will be helpful.

Regards,
-Glenn
0
 

Author Comment

by:tomrector
ID: 40348917
Have not been able to get your soultion  to work...
Not sure lookup table will work....

Actual time are by minute.  0701..0702,,0703 ect.

The intent is to have "any time listed" (in Col "J" as described above)  on Saturday (Col "I")  between 0700  and 1050 to show up as T1,
anytime between  1051 and  1400  show as T2  
and any time on Sat between 1401 at 1700  to show as T3.
and any time after 1701 as T4
I tried to attach the actual chart.
tomchart.jpg
0
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 2000 total points
ID: 40349411
Your example table is just a transposed version of the one I created.  It's easy enough to revise the formulas to refer to your table instead and still produce correct results.

I used your table and added "extreme" times to represent boundaries between "sun up" and "sun down" times (in other words, times before or after any possible tee time).  I then changed the formulas to pick up the correct T-values.

Additionally, I have listed all days and "Holiday" and minute-by-minute listings around each Day Part (DP) so that you can see how the formulas work and that they are, in fact, returning your expected T-Values.

I've also added a new set of functions in the Day column on Sheet1 that will return the day of the week or "Holiday" if the date falls on a pre-described holiday (see list in Sheet2).

Regards,
-Glenn
EE-TValues.xlsx
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40368880
Hi,
Did you have a chance to review/test my latest solution?  I wanted to also point out that columns K, L, & M aren't necessary and can be deleted from the workbook.  The reason they're left in is to help you see the lookup process (using the MATCH function) that derives the correct column and row on the table.

If you have any questions, please let me know.  

Regards,
-Glenn
0
 

Author Comment

by:tomrector
ID: 40372605
The T1- T4  are  all Day Parts,  relates to the fees we charge during the the "Tx" time range.  No one day has T1 thru T5,  i.e  Mondays are T4-T5  while Saturdays have T1-T2-T3.
So I ignored the least populated "Ts"  on any given day with more than 2, since I could not get the IF/AND/OR to work  for me.
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40373183
Tom,

My solution follows your described rules above.  If you create a PivotTable on the sample data sheet in my example workbook, you'll see that all those rules are applied:
T-values by day
Did you mean to close this question?

-Glenn
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40412546
My second solution (40349411) used the Asker's own rules table (post 40348917, attached image) in a revised workbook that does an indexed lookup to derive the correct T-Value for any given day of the week and time, including holidays.   I believe it is a complete and workable solution and should be accepted.

Thanks,
-Glenn
0
 
LVL 1

Expert Comment

by:Richard Francis
ID: 40412852
I agree with Glenn. He's put a lot of work into this question and provided a thorough solution.
0

Featured Post

WordPress Tutorial 4: Recommended Plugins

Now that you have WordPress installed, understand the interface, and know how to install new parts, let’s take a look at our recommended plugins.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When setting up new project requests for our site, one of the most powerful tools our team has available to use is Axure (http://www.axure.com/). It’s a tool for creating software and web prototypes that can function and interact as if it were the a…
Introduction In this tutorial, I'll explain how to create an animated progress meter in a wireframe prototype developed using Axure RP 7.0 - a leading prototyping tool for designing web sites and software. (For more information about Axure and gett…
The purpose of this video is to demonstrate how to Import and export files in WordPress. This will be demonstrated using a Windows 8 PC. Go to your WordPress login page. This will look like the following: mywebsite.com/wp-login.php : Click on Too…
The purpose of this video is to demonstrate how to prevent comment spam on a WordPress Website. This will be demonstrated using a Windows 8 PC. Plugin Akismet will be used. Go to your WordPress login page. This will look like the following: myw…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question