Non-reconciling formula in Nested IF AND statements

[SewerUrchin]

Hello:

I worked for over an hour last night trying to get this darn formula to work and have been unsuccessful. I know it is probably something stupid, but my brain won't get to the solution for some reason.

I split my home with another and we use a list to keep track of expenses that we both should get socked with. In some cases the charge is simply 50% of the total and in others it is a specific $ amount or % of the charge. I've attempted to auto calculate the amount off of the total depending on who charged the expense and on the % or $ amount being pushed to the other party. It is precisely this formula that won't reconcile. Here is the formlua as it sits right now:

=IF(AND((B4="Tom"),(F4="$")),((-1)*G4)),(IF(AND((B4="Tom"),(F4="%")),((-1)*(E4*(G4/100)))),(IF(AND((B4="Jeff"),(F4="%")),(E4*(G4/100)),G4)))

Column B has who charged the expense.
Column F holds whether the amount in G4 is a dollar amount or a percentage.
Column G holds the dollar amount to charge or the result of the percentage amount in F times the total amount of the charge in E.
The eventual result is placed in Column G.
The boldfaced formula is the place Excel gets huffy about. (It does reconcile on its own outside of the formula)

Negatives are charges to Jeff and the positives are charges to Tom. Really if the total of all of the calculations ends up being negative, Tom owes Jeff. If the total is positive, Jeff owes Tom. The total of all charges will float both positive and negative depending on the spending over time.

If this board allows an image post, here is the spreadsheet as I see it:

 

[Kelbo]

Hi,

It appears that you have overdone the brackets. You do not need backets in these particular formulae and not in the and statements.

Losing the brackets makes it easier to follow!

Try:
=IF(AND(B4="Tom",F4="$"),-1*G4,IF(AND(B4="Tom",F4="%"),-1*E4*G4/100,IF(AND(B4="Jeff",F4="%"),E4*G4/100,G4)))

Kelbo

 

[SewerUrchin]

YOU ARE AWESOME!!!! Thank you! It works perfectly! I must admit though I am a tad embarassed about this. NEVER before have I been befuddled by parethesis! It got me this time though. Maybe its the cold here in Indianapolis! None-the-less, thank you a bunch!

Urchin