Solved

Using multiple"if" "ands'

Posted on 2014-09-22
14
114 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
  • 8
  • 2
  • 2
14 Comments
 
LVL 1

Expert Comment

by:Richard Francis
Comment Utility
Hi Tom,

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

Richard.
0
 
LVL 27

Expert Comment

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

Expert Comment

by:Glenn Ray
Comment Utility
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
 
LVL 27

Expert Comment

by:Glenn Ray
Comment Utility
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
Comment Utility
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
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 27

Accepted Solution

by:
Glenn Ray earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
I agree with Glenn. He's put a lot of work into this question and provided a thorough solution.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

When deciding to adopt any help desk solutions many factors should be explored before taking decisions. This will change from business to another but in general there are some kind of rule of thumb. Here are some quick tips: Do we need only ticket…
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 insert an Iframe into 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 : Open Page or Post…
The purpose of this video is to demonstrate how to set up an RSS Feed on a WordPress Website. This will be demonstrated using a Windows 8 PC. Feedburner will be used for this demonstration. Go to your WordPress login page. This will look like the…

728 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now