complex if statement

**snoopy990** · 06-02-2009, 12:33 PM

Hello,

I've never had any formal training in Excel; everything has been self-taught. I'm working on a complex nested if statement, but I'm told that I have too many arguments. That may well be the case; I just thought I'd see if any experts out there knew of a way to put the following pseudocode into one cell on the second page of an Excel spreadsheet. It would make things a lot easier for me! If there's not a way to simplify this enough to put it in one cell, I can understand - I just wanted to come here to find out for sure.

It looks more complicated than it may need to be. Basically what I'm trying to do is calculate pay for mileage while factoring in the number of participants. In other words, if the mileage was between 0 and 50 miles, and there were 5 or less people, then this cell should be $35.00. But if it's between 0 and 50 and there were more than 5, then this cell should display 175/the number of people (175 being 35 * 5). In other words, more than five people would receive an equal amount of the total pay for five people (this is because five people are the standard, but there are instances in which six or seven could participate and I was hoping to avoid hardcoding that).

The rest of the if statements simply follow that same formula, but for different mileage/pay rates.

Any information would be greatly appreciated.

Thanks,

Beau

---------------

If Sheet1!H2 = “x”
If A2 is between 0 and 50
If the total count of cells in the range of H2:H25 = “x” on Sheet 1 <= 5
Value = 35
Else
Value = (175/sheet 1 total cell count = “x” in the range of H2:H25)
Endif
Else
If A2 is between 51 and 100
If the total count of cells in the range of H2:H25 = “x” on Sheet 1 <= 5
Value = 45
Else
Value = (225/sheet 1 total cell count = “x” in the range of H2:H25)
Endif
Else
If A2 is between 101 and 150
If the total count of cells in the range of H2:H25 = “x” on Sheet 1 <= 5
Value = 60
Else
Value = (300/sheet 1 total cell count = “x” in the range of H2:H25)
Endif
Else
If A2 is between 1 51 and 200
If the total count of cells in the range of H2:H25 = “x” on Sheet 1 <= 5
Value = 45
Else
Value = (225/sheet 1 total cell count = “x” in the range of H2:H25)
Endif
Else
If A2 is >= 201
If the total count of cells in the range of H2:H25 = “x” on Sheet 1 <= 5
Value = (A2 * .20) + 50
Else
Value = (((A2 * .20) + 50) * 5)/sheet 1 total cell count = “x” in the range of H2:H25)
Endif
Endif
Endif
Endif
Endif
Endif
Endif

**NBVC** · 06-02-2009, 12:36 PM

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.

**NBVC** · 06-02-2009, 12:36 PM

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

**NBVC** · 06-02-2009, 12:36 PM

Your post does not comply with Rule 5 of our Forum RULES. We have Seven question forums: Miscellaneous, General, Programming, Worksheet Functions, Charting, Excel 2007 Help and New Users. Please choose the appropriate forum, and post your question in ONLY one forum.