Non-reconciling formula in Nested IF AND statements

SewerUrchin

New Member
Joined
Jan 25, 2005
Messages
24
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:
 
Last edited:

Kelbo

Board Regular
Joined
Jun 16, 2007
Messages
224
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

New Member
Joined
Jan 25, 2005
Messages
24
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
 

Forum statistics

Threads
1,082,478
Messages
5,365,780
Members
400,849
Latest member
candy2052

Some videos you may like

This Week's Hot Topics

Top