Multiple if statements with upper/lower bounds

**crail5** · 01-13-2013, 02:40 AM

Hi --

Looking for some help on an if formula. I've attached the excel sheet for reference.

To give some background, I am trying to create a formula that will enable me to easily create a schedule for bowling teams at an upcoming tournament. There are 70 lanes at the center, with 30 lanes devoted to women and the other 40 devoted to men. There are 6 games bowled during the tournament, and teams switch lanes (i.e. they skip 2 pairs of lanes) after every game, moving to the right if they are on an even lane and to the left if they are on an odd lane.

Here are a couple examples to make that more clear. A men's team starts on lane 56 for game one. This team will be bowling on lane 62 for game two (skipping two pairs of lanes equates to 6 lanes). If a women's team starts on lane 11 for game one, that team will bowl on lane 5 for the second game. It becomes tricky when teams are at either of the extremes of their lanes. For instance, if a men's team started on lane 68 for game 1, then it would move to lane 44 for game 2. The team must move back into the middle of the bowling center, given that there are only 70 lanes. If a women's team started on lane 1 for game one, then it would move to lane 25 for game two.

Please let me know if that makes sense. I've been playing around with the formula and can't get it quite right. I think the problem right now is that my odd lanes are turning up even lanes, which isn't right. Much appreciate any help!

**zbor** · 01-13-2013, 03:18 AM

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.