Hi, I am trying to build a formula where when a set of criteria is met then a customer is charged a different rate. For instance:

I have the following cells:

J3 states whether a site is under maintenace (yes for maintenance no if not)

O3 states whether a technician was on site or the work was remote (yes for on site no for remote)

R3 states the priority (billable repair, MAC, Emergency Service, Professional Opion & Other - these are the only categories)

X3 is the total hours rounded up to the nearest 15 minutesI would like to build a formula that if J3=YES and O3=YES and R3=Billable repair then it would charge X3*$65, or if J3=YES and O3=YES and R3= MAC then it would charge X3*$75 or if J3=NO and O3=YES and R3 = MAC then it would charge X3*85 and so on for all the variable situations.

Is it possible to build a formula with so many variables or would I be better off to sort manually and build the formulas manually? I hope my question is somewhat clear.

Thanks so much for any help you can offer.

First, when posting in a Help forum such as this, please try to use a Subject Line that tells us something about your question. If everyone used a generic Subject Line such as "Help With Excel" we wouldn't be able to tell one question from another and the Archives would essentially be useless. I have edited the subject line of your post to show you what I mean. Scan the list of questions in this forum to see the types of Subject Lines that others have used.

As to your question, you could certainly create a Nested IF to check all of your conditions, but it would get very ugly very quickly. For example, this formula only covers YES, YES with Billable Repair or MAC:

=IF(AND(J3="YES",O3="YES"),IF(R3="Billable Repair",X3*65,IF(R3="MAC",X3*75,"")))

Imagine how complicated it would look if you tried to include all combinations of your conditions.

A better method might be to use a VLOOKUP table which is based on the Concatenated combinations of your criteria. If you are not familiar with VLOOKUP, you should read the Help files on the function. It is very powerful and can be used for many other situations than this one. I am going to use the names of the VLOOKUP

arguments(italicized) in my explanation, so you'll probably need to read the Help file in order to make sense of it.In your case, you would set up a

table_arraywhere the first column would be the Concatenated combinations of your criteria. I'll just show you first 2 (to replace the IF statement shown above) and you can take care of the rest. I am going to put mytable_arrayin A1:B2, you can put yours where ever you'd like.A B 1 YESYESBillable Repair 65 2 YESYESMAC 75The formula to use would be this:

=X3 * VLOOKUP(J3&O3&R3, $A$1:$B$2, 2, 0)

This will create a

lookup_valuelike YESYESBillable Repair or YESYESMAC based on the values in J3, O3 and R3.It will then lookup that string in the first column of your

table_arrayand return the value found incol_index_num2.So, if it looks up YESYESMAC, it will return 75 and evaluate X3 * 75

Note:

col_index_num2 refers to the 2nd column of thetable_array, not the 2nd column of the worksheet. In this case, they are the same, but they don't have to be. (See the Help files for a further explanation)Again, you'll need to understand how VLOOKUP works in order to use this method, so study up! ;-)

BTW...one of the advantages of VLOOKUP is the ability to change the values in the

table_arrayand never have to mess with the formula. For example, if your price changes from 65 to 67.50, all you have to do is change B1 and all of your formulas will reflect the change.If you used a complicated Nested IF, you have to change the 65 to 67.50 everywhere it occurred.

Come on back if you need any further assistance.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Sorry for the generic in my subject line. I should have thought of that when I posted. I will definitley study up on the VLOOKUP option; it certainly appears to be the best solution for what I am trying to accomplish. Thanks again!

Ask Your Question

Weekly Poll

Do you think Microsoft's new Surface Go will be a hit?

Discuss in The Lounge

Poll History