Nested If statements

[Domroy]

Hi there! I am stumped.

I run a Christmas Caroling Business. In row 1, I have a running total of each singer's pay. My table starts at row 2. Headers are as follows starting in column A (cell A2)

DAY
DATE
EVENT
TIME
DEPOSIT
BALANCE
DUE
TOTAL FEE
OTHER
COMMENT
PAY
SOPRANO (COLUMN L)
ALTO (M)
TENOR (N)
BASS (O)

Then, my sopranos are listed in P-AA, Altos are listed in AB-AL, Tenors are AM-AV and Basses are AW-BE.

Then I have about 200 rows of events below them. To save myself time, I have the following formula in all the rows underneath each individual name: =IF(OR($L85=$P$2:$AL$2,$M85=$P$2:$AL$2),(0.84*$H85/4),"") - I happened to copy it from row 85...but you get it.

That formula looks to see if the name in the "Soprano" column (column L), matches the header (row 2), and if it does, it looks at the total fee column (column H), and calculates the formula for individual pay. So if I assign the gig to Judi, it puts Judi's individual pay in her column only, and leaves the other sopranos blank for that gig. As it should be. This works perfectly.

However, I've now opened a branch on the east coast. And there is a lot of travel involved there, so we have to add a travel fee. The problem is, we do not take a tithe to the business for the travel fees. The singers get 100 percent of the travel fee (but divided by 4, because the singers go out in quartets, in case that wasn't clear).

SO...I'm trying to build a new formula that does the following:
- looks to match the singer name in the assignment section (L-O), to the header in row 2.
- if it matches, it looks to see if there is a number in the FEE column (column h).
- IF SO, the number that is returned in that cell is the fee (H85*.84)/4 (company takes 16 percent and then the rest is divided amongst the singers).
- if the FEE column is blank, then I want it to look in the "Other" column (column I), and return that number divided by 4 (no tithe taken out).
- if they're both blank, then I want the cell to be blank.

So you see, I'm well on my way, but because of our new branch - I spend a lot of time manually entering the travel payment into the singers' columns, because the calculation is different.

What do you think? I know someone can solve this. I'm just overthinking it at this point and need to take a break.

THANK YOU!

Judi

 

[RSpin]

If I'm understanding what you want correctly, then this may work for you:

=IF(OR($L85=$P$2:$AL$2,$M85=$P$2:$AL$2),if($H85<>"",(0.84*$H85/4),if($I85<>"",$I85/4,"")),"")

The original code simply returns the modified amount listed in column H. This changes that to check if there is information in column H, if so then it uses the formula you provided. If there is nothing in column H, then it looks to see if there is information in column I, and if so, returns that amount divided by 4. If both are blank, then the cell will be empty.

I hope this helps

 

[Domroy]

YES YES YES!!!! This is it!!! Thank you so much!!!!!