Another IF function with multiple conditions

Hello,

I'm new here and pretty much a novice when it comes to formulas. My spreadsheet looks like this:

Column A: Individual
Column B: Not used in formula
Column C: Not used in formula
Column D: Location
Column E: Duration in Minutes
Column F: Pay (Column E)*(Column G)
Column G: Formula (simplified version listed below)

=IF(AND(A3="individual1",D3="Location1",E3=90),0.66666666667,
IF(AND(A3="individual1",D3="Location2",E3=90),0.5,
IF(AND(A3="individual1",D3="Location1",E3=60),0.66666666667,
IF(AND(A3="individual1",D3="Location2",E3=60),0.5,
IF(AND(A3="individual1",D3="Location1",E3=45),0.66666666667,
IF(AND(A3="individual1",D3="Location2",E3=45),0.5,
IF(AND(A3="individual1",D3="Location1",E3=30),1,
IF(AND(A3="individual1",D3="Location2",E3=30),0.75,
IF(AND(A3="individual1",D3="Location3"),0.85,
0
)))))))))

To be clear of what I'm trying to achieve:

When in location 1 & 2 (Column D), the pay for each individual is a different rate depending on the duration in Column E, but when in Location 3, the rate is consistent no matter what the duration.

My question is:

How do account for individuals who aren't individual1? That is, my understanding of the above formula is that I need to list each individual that will appear in Column A for it to work. What if I just want to apply the above to individual1, but apply some other calculations to all other individuals?

I had tried adding these lines (with their corresponding closed-parentheses at the end)

IF(AND(D3="location1",E3=90),0.8333333333,
IF(AND(D3="location",E3=90),0.66666666667,
IF(AND(D3="location1",E3=60),0.8333333333,
IF(AND(D3="location2",E3=60),0.66666666667,
IF(AND(D3="location1",E3=45),0.8333333333,
IF(AND(D3="location2",E3=45),0.66666666667,
IF(AND(D3="location1",E3=30),1.25,
IF(AND(D3="location2",E3=30),1,
IF(D3="location3",1.1333333,

But it doesn't work. I'm guessing it's some sort of OR, but I don't yet grasp how.

I should note that my formulas are all on one line in my spreadsheet, I just have them on separate lines to make it easier to find my way around.

Thanks for the help!

Thanks for the replies, everybody.

First off, I'm still trying to wrap my head around how to use a VLOOKUP for my purposes. Any help in this direction is definitely appreciated.
Secondly, I understand that there are redundancies in my formula and very much appreciate the help to remove them so that I'm working with the cleanest, leanest formula, however there is a reason for some of the repeated stuff.

Let me take a moment to better illustrate what I've got going on because I realize that I just didn't give enough information.

Here is an example spreadsheet:

Example.xlsx

In the spreadsheet we can see on the 'Pay' worksheet that individuals in Column A (which we will now call Contractors) may be on one of five pay scale tiers. The Tier each Contractor is currently on is specified in the second worksheet titled 'Tier'. On a sidenote, I'm guessing that this is likely where VLOOKUP will come in handy in referencing this table.

These Contractors meet with Clients in any of 8 locations, each of which pays the Contractor at a different rate. Locations 1 & 2 have different rates depending on the 4 durations, whereas Locations 3-8 have a fixed rate independent of duration.

So to review:

1. Contractors on any of 5 different Pay Tiers
2. 8 Locations (and their respective rates)

1. Locations 1-2 (different rate for each of the 4 durations)

2. Locations 3-8 (fixed rate independent of duration)

This gives us 70 different permutations:

=IF(AND(A3="Tier1",D3="Location1",E3=90),0.66666666667,
IF(AND(A3="Tier1",D3="Location2",E3=90),0.5,
IF(AND(A3="Tier1",D3="Location1",E3=60),0.66666666667,
IF(AND(A3="Tier1",D3="Location2",E3=60),0.5,
IF(AND(A3="Tier1",D3="Location1",E3=45),0.66666666667,
IF(AND(A3="Tier1",D3="Location2",E3=45),0.5,
IF(AND(A3="Tier1",D3="Location1",E3=30),1,
IF(AND(A3="Tier1",D3="Location2",E3=30),0.75,
IF(AND(A3="Tier1",D3="Location3"),0.85,
IF(AND(A3="Tier1",D3="Location4"),0.7,
IF(AND(A3="Tier1",D3="Location5"),1.1,
IF(AND(A3="Tier1",D3="Location6"),0.9,
IF(AND(A3="Tier1",D3="Location7"),1.35,
IF(AND(A3="Tier1",D3="Location8"),1.1,
IF(AND(A3="Tier2",D3="Location1",E3=90),0.7083,
IF(AND(A3="Tier2",D3="Location2",E3=90),0.54167,
IF(AND(A3="Tier2",D3="Location1",E3=60),0.7083,
IF(AND(A3="Tier2",D3="Location2",E3=60),0.54167,
IF(AND(A3="Tier2",D3="Location1",E3=45),0.7083,
IF(AND(A3="Tier2",D3="Location2",E3=45),0.54167,
IF(AND(A3="Tier2",D3="Location1",E3=30),1.0625,
IF(AND(A3="Tier2",D3="Location2",E3=30),0.8125,
IF(AND(A3="Tier2",D3="Location3"),0.9209,
IF(AND(A3="Tier2",D3="Location4"),0.7583,
IF(AND(A3="Tier2",D3="Location5"),1.1917,
IF(AND(A3="Tier2",D3="Location6"),0.975,
IF(AND(A3="Tier2",D3="Location7),1.4626,
IF(AND(A3="Tier2",D3="Location8"),1.1917,
IF(AND(A3="Tier3",D3="Location1",E3=90),0.75,
IF(AND(A3="Tier3",D3="Location2",E3=90),0.583,
IF(AND(A3="Tier3",D3="Location1",E3=60),0.75,
IF(AND(A3="Tier3",D3="Location2",E3=60),0.583,
IF(AND(A3="Tier3",D3="Location1",E3=45),0.75,
IF(AND(A3="Tier3",D3="Location2",E3=45),0.583,
IF(AND(A3="Tier3",D3="Location1",E3=30),1.125,
IF(AND(A3="Tier3",D3="Location2",E3=30),0.8745,
IF(AND(A3="Tier3",D3="Location3"),0.9911,
IF(AND(A3="Tier3",D3="Location4"),0.8162,
IF(AND(A3="Tier3",D3="Location5"),1.2826,
IF(AND(A3="Tier3",D3="Location6"),1.0493,
IF(AND(A3="Tier3",D3="Location7"),1.5742,
IF(AND(A3="Tier3",D3="Location8"),1.2826,
IF(AND(A3="Tier4",D3="Location1",E3=90),0.79167,
IF(AND(A3="Tier4",D3="Location2",E3=90),0.625,
IF(AND(A3="Tier4",D3="Location1",E3=60),0.79167,
IF(AND(A3="Tier4",D3="Location2",E3=60),0.625,
IF(AND(A3="Tier4",D3="Location1",E3=45),0.79167,
IF(AND(A3="Tier4",D3="Location2",E3=45),0.625,
IF(AND(A3="Tier4",D3="Location1",E3=30),1.1875,
IF(AND(A3="Tier4",D3="Location2",E3=30),0.9375,
IF(AND(A3="Tier4",D3="Location3"),1.0625,
IF(AND(A3="Tier4",D3="Location4"),0.875,
IF(AND(A3="Tier4",D3="Location5"),1.375,
IF(AND(A3="Tier4",D3="Location6"),1.125,
IF(AND(A3="Tier4",D3="Location7"),1.6876,
IF(AND(A3="Tier4",D3="Location8"),1.375,
IF(AND(A3="Tier5",D3="Location1",E3=90),0.8333333333,
IF(AND(A3="Tier5",D3="Location2",E3=90),0.66666666667,
IF(AND(A3="Tier5",D3="Location1",E3=60),0.8333333333,
IF(AND(A3="Tier5",D3="Location2",E3=60),0.66666666667,
IF(AND(A3="Tier5",D3="Location1",E3=45),0.8333333333,
IF(AND(A3="Tier5",D3="Location2",E3=45),0.66666666667,
IF(AND(A3="Tier5",D3="Location1",E3=30),1.25,
IF(AND(A3="Tier5",D3="Location2",E3=30),1,
IF(AND(A3="Tier5",D3="Location3",1.1333333,
IF(AND(A3="Tier5",D3="Location4",0.933333333,
IF(AND(A3="Tier5",D3="Location5",1.46666667,
IF(AND(A3="Tier5",D3="Location6",1.20,
IF(AND(A3="Tier5",D3="Location7",1.8,
IF(AND(A3="Tier5",D3="Location8",1.46666667,
0
))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))

But an IF/AND doesn't seem to work like that, so the above formula is broken.

About the redundancies, you'll see that within each Tier, Location1 90, 60, & 45 are all the same rate. Same goes for Location2 90, 60, & 45. There are other similarities such as Location5 & 8 having the same rate within each respective Tier.

So, now how do I manage all of this?

If VLOOKUP is the way to go, how would I set it up using this specific information?

Thanks again for everyone's interest in solving this. I really do appreciate the help of this community!

Thanks again FDibbins.

The purpose of the Fields worksheet is to determine the Tier that each Contractor is on. The Tier they're on changes throughout the year as they progress through stages/accomplishments, so this is simply the master list.

The relationship of the two tables is that at any given time some Contractors are on Tier 1, some are on Tier 2, some are on Tier 3, and so on. For example, row 6 of the Tiers worksheet shows that Contractor 5 is currently on Tier 2. This means that the Tier 2 rates will apply to that contractor. So on the Pay worksheet, rows 15-17 need to have Tier 2 rates applied, because those are the rows submitted by Contractor 5.

Contractors submit data via webform that has the values A-E. I then need to work with that data in a spreadsheet to calculate each Contractor's pay on a monthly basis.

Up until this point, there has only been one Tier so the formula has been easy:

=IF(AND(D1="Location1",E1<>30),0.8333333333,
IF(AND(D1="Location2",E1<>30),0.66666666667,
IF(AND(D1="Location1",E1=30),1.25,
IF(AND(D1="Location2",E1=30),1,
IF(D1="Location3",1.133333333,
IF(D1="Location4",0.933333333,
IF(D1="Location5",1.466666667,
IF(D1="Location6",1.2,
IF(D1="Location7",1.8,
IF(D1="Location8",1.466666667,
0))))))))))

But now that we've added the 4 other Tiers things have gotten complicated.

I hope this helps to clarify. I'm happy to continue explaining as needed. If this isn't what you were asking, just give me some more guidance and I'll walk through more of the process.

You could use a formula like this in H3:

=VLOOKUP(A3,Tiers!A:B,2,0)

to get the tier for each contractor.

As regards your main request, I have taken your long multi-IF formula from Post #6 and converted it into a 2-D table, shown on the Tiers sheet. Note that Location1_x and Location2_x are for the durations above 30 minutes. Then you can get the rate by means of this formula in G3:

=INDEX(Tiers!$E$2:$I$11,MATCH(D3&IF(AND(--RIGHT(D3)<3,Pay!E3>30),"_x",""),Tiers!$D$2:$D$11,0),MATCH(VLOOKUP(A3,Tiers!A:B,2,0),Tiers!$E$1:$I$1,0))

which can then be copied down (no need for the formula in H3 - I just gave that as an example of VLOOKUP).

Hope this helps.

Pete

Wow, this is great! Thank you, Pete.

Your attachment works great and appears to be the right direction for what I'm looking for. I have three questions for you:

1. Would you be so kind to go through the formula for Column G in words and explaining how it works? I don't fully understand it, so this will help me edit it to my needs. If this is a case of RTFM, then I appreciate your patience with me.

2. I'm actually getting the following error when I copy it into my working spreadsheet:

Error: Function UNARY_MINUS parameter 1 expects number values. But 'l' is a text and cannot be coerced to a number.

I don't see any minus's in the formula, so I'm confused as to what the error is referring to.

Here is my version of the formula. I changed 'Pay' to 'March' because the spreadsheet I use actually has one tab for each month in the year.

=INDEX(Tiers!$E$2:$I$11,MATCH(D3&IF(AND(--RIGHT(D3)<3,March!E3>30),"_x",""),Tiers!$D$2:$D$11,0),MATCH(VLOOKUP(A3,Tiers!A:B,2,0),Tiers!$E$1:$I$1,0))

But even when using your original formula, and naming the worksheet 'Pay', I still get the same error.

3. There's also another issue due to the way I set up my example:

The locations are actually not submitted by the Contractors as:

Location1
Location1_x

The two examples above are actually both submitted as Location1. The difference is that Location1_x is submitted as Location1 but a value of 30 in Column E. How do I go about incorporating this into the formula?

Alright, thanks again!

1. This is the formula in the file I attached:

=INDEX(Tiers!$E$2:$I$11,MATCH(D3&IF(AND(--RIGHT(D3)<3,Pay!E3>30),"_x",""),Tiers!$D$2:$D$11,0),MATCH(VLOOKUP(A3,Tiers!A:B,2,0),Tiers!$E$1:$I$1,0))

INDEX is more flexible than VLOOKUP and it is often used in conjunction with MATCH to locate the cell that you want to return data from. In general, you can think of it like this:

=INDEX(range,row,column)

The column parameter is thus:

MATCH(VLOOKUP(A3,Tiers!A:B,2,0),Tiers!$E$1:$I$1,0)

The VLOOKUP function is the same as the one I gave you earlier, so it returns Tier1, or Tier2 up to Tier5, as defined in your table in columns A and B. We then try to find a match between that and the values in the Tiers sheet from E1 to I1, i.e. it finds the matching Tier number and uses that column to identify where the data is located.

The row function is:

MATCH(D3&IF(AND(--RIGHT(D3)<3,Pay!E3>30),"_x",""),Tiers!$D$2:$D$11,0)

so this takes the location from D3 and tries to find a match in D2:D11 of the Tiers sheet. There is a slight complication in that "_x" can be tagged on to the location if it is either Location1 or Location2 AND if the duration (given in E3) is greater than 30. This function finds the matching row.

So, the INDEX formula will return the value from the range E2:I11, corresponding to the row and column parameters.

2. I see you are using Google Docs, so that might work slightly differently than Excel. This part of the formula:

AND(--RIGHT(D3)<3,Pay!E3>30),"_x","")

is checking for the right-most digit of the Location - if it is less than 3 (i.e. for Location1 or Location2) AND if E3 is above 30, then _x gets added onto the end of the location. However, the RIGHT function in Excel returns a text value, and so one way to convert it into a number is to apply the double-unary minus in front of it. Your error message seems to imply that you can't do that in Google Docs. Are you actually entering Location followed by a number, or are you using the actual place names like Washington?

You can omit the Pay (or March) from that part of the formula, as you are looking for E3 on the same sheet as the formula, so you don't need the sheet name in there.

3. Hopefully you can realise from what I have said above that the formula automatically tags on the _x where it is needed, so you don't need to worry about it.

Anyway, If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, I would like to inform you that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

Okay, thanks for the thorough explanation, Pete. This is all really appreciated.

Now that I have gone through it all, I can see where the error in my spreadsheet is. Google is having no trouble with your example, as it works great until I enter in my actual location names. That is, the locations submitted by the contractors are not the generic examples I had given:

Location1, Location2, Location3, Location4, Location5, Location6, Location7, Location8

They're instead specific names with the following order corresponding to the above list:

Housecall, Studio, Group2 Housecall, Group2 Studio, Group3 Housecall, Group3 Studio, Group4 Housecall, Group4 Studio

This means that this part of the formula can't work with those names:

AND(--RIGHT(D3)<3,Pay!E3>30),"_x","")

Because they're not numbered sequentially. With this list of unique names following no particular order, how would one handle this part of the formula?

So "Housecall" and "Studio" are the locations for which a different rate applies above 30 minutes?

Pete

That is correct.

Okay, I'll make the necessary changes to the file in Post #9 and get back to you.

Pete

See attached file. I've changed the location names, and changed the table, and had to change the formula in G3 to this:

=INDEX(Tiers!$E$2:$I$11,MATCH(D3&IF(AND(OR(D3="Housecall",D3="Studio"),Pay!E3>30),"_x",""),Tiers!$D$2:$D$11,0),MATCH(VLOOKUP(A3,Tiers!A:B,2,0),Tiers!$E$1:$I$1,0))

Hope this helps.

Pete

Brilliant! Thank you so much for your help, Pete.

If you are still up for the challenge, I have one last twist to the saga:

We have an instance where a specific Contractor submits record of a specific Client+Location+Length and a non-standard rate needs to be applied. In order to handle this anomaly, I had previously added this to my original formula:

IF(AND(A3="Contractor1",C3="Client1",D3="Housecall",E3=30),1.91666666667,

But I just don't understand how to incorporate this wildcard into the new INDEX/MATCH formula.

As always, much gratitude for any help.

You could do it like this in G3:

=IF(AND(A3="Contractor1",C3="Client1",D3="Housecall",E3=30),1.91666666667,INDEX(Tiers!$E$2:$I$11,MATCH(D3&IF(AND(OR(D3="Housecall",D3="Studio"),Pay!E3>30),"_x",""),Tiers!$D$2:$D$11,0),MATCH(VLOOKUP(A3,Tiers!A:B,2,0),Tiers!$E$1:$I$1,0)))

The bits that I've added to what is already in G3 are shown in red, i.e. the partial formula you quote in Post #17 (including the comma) goes at the beginning PLUS a close-bracket at the end.

Hope this helps.

Pete

Yes! I don't know how I had missed trying to place it at the beginning.

Thank you again, Pete. Cased closed.

=IF(AND(A3="Pete helps", B3="Pete helps again", C3="Pete helps yet again"),"I am a Happy Camper","Banging my head against the wall")

I know that I marked this as SOLVED (and for the most part, it is) but in order for the rest of my spreadsheet to work I see now that I need our formula to output 0 when empty instead of --.

That is, the formula as it now stands is:

=IF(AND(A3="Contractor1",C3="Client1",D3="Housecall",E3=30),1.91666666667,INDEX(Tiers!$E$2:$I$11,MATCH(D3&IF(AND(OR(D3="Housecall",D3="Studio"),E3>30)," 60+",""),Tiers!$D$2:$D$11,0),MATCH(VLOOKUP(A3,Tiers!A:B,2,0),Tiers!$E$1:$I$1,0)))

I figured that I was to just throw a ,0 in somewhere, but the places I've tried either have no effect, or they break the formula.

Any guidance is appreciated.

I can't see any circumstances where that formula will return "--". Is this something you have put in the lookup table? I would expect the formula to return #N/A if any of the values are missing in A3, C3 or D3, and if you want to avoid that you can wrap IFERROR( .... ,0) around the whole formula (not sure if Google Spreadsheets supports that).

Hope this helps.

Pete

IFERROR did the trick!

Thanks for everything, Pete!