Multiple Ifs in an excel formula

[Dylan Ross]

I am trying to figure out how to set up a formula that would perform a calculation based off of data in 3 separate cells with one or more criteria being met:

Cells are "Department Number", "Person's Name", "Field Ticket Total" and "Calculated Field"

1st criteria to be met:

Last 3 digits of department number = "080"
Person's name = "John Doe"
If these two criteria are met, then take the Field Ticket Total ($2,000) and multiply it by 0.02.

2nd criteria to be met:

Last 3 digits of department number = "080"
Person's name = "Jane Doe"
Since the department number = "080", but the person's name IS NOT "John Doe", I want the Field Ticket Total ($2,000) to be multiplied by 0.01.

3rd criteria to be met:

Last 3 digits of department number do not end in "080"
Person's name = "Jane Doe"
Since the department number is not "080" and the person's name is not "John Doe", I want the Field Ticket Total ($2,000) to be multiplied by 0.005.

The above would produce the information in the calculated cell for each row based on the three criteria above.


Thanks in advance!

Dylan Ross

 

[Rx_]

Will borrow and modify to get you started:

The IF function provides decision-making capability to a worksheet. It Uses 3 arguments:

• Evaluated condition (value or formula result with TRUE or FALSE)
• Displayed value if condition is TRUE
• Displayed value if condition is FALSE

Example: returns 1 if cell A1 contains "A". If cell A1 does not contain "A", it returns an empty string. (Needed for your Person's Name example).
=IF(A1="A",1,"")Your question will require another "nest" of the IF functions within a formula. Use an IF function as the second argument for an IF function. Example:
=IF(A1="A",1,IF(A1="B",2,IF(A1="C",3,"")))Evalueate cell A1. If it contains "A" (or in your case "John Doe", the formula returns 1. If it doesn't contain "A", then the second argument is evaluated. Another IF function is in each of the the return value.
Nesting can go up to seven levels of IF functions. Example:
=IF(A1="A",1,IF(A1="B",2,IF(A1="C",3,IF(A1="D",4, IF(A1="E",5,IF(A1="F",6,IF(A1="G",7,IF(A1="H",8,""))))))))B.T.W. Excel 2007 and later can have 64 nesting levels. Before writing more than 3 levels, ask a question about alternatives to Nesting.

Question: where are you obtaining the data from? Is it coming from an Access DB or other DB?

 

[Gasman]

You do not say what the cell should hold if it is not "080" and is John Doe?

I have decided in this formula to show the whole amount(£2000) Cell A3 holds the amount.

Amend to suit

=IF(A1 = "080",IF(A2 = "John Doe",A3 * 0.02,A3 * 0.01),IF(A2 <> "John Doe",A3 * 0.005,A3))

HTH

 

[Rx_]

The slippery path of using nested If statements....
The question is: Where is the data coming from?
If it is a database, using some basic SQL language can work wonders.
If it is hand-entered, then using a V-Lookup or other options can lower the cost of maintence over the long run.

Typically, my "it depends" would start with the question of "where does the data come from". Otherwise, this use to be called a Premature Solution in the Design Cycle.
In otherwords, is the objective just responding to a single situation of data one time on a spreadsheet or is it someting that will be reused in a business process.