Rusty at Best with Excel Multiple If Function

[legreenwood]

I have a situation in which I need to calculate Z (teams) *AA if BOTH X and Y = 1, and the same for the alternate values of Z (2 and 3) while keeping them separate because I need to later in the spreadsheet show the value for each record by team.

X Y Z AA
1 1 3547 1
2 0 9987 5
3 1 1597 4
2 0 155667 3
3 1 3247 2
1 0 354898 0

Basically, I need to know Z*AA if Y=0, and Z-(Z*AA%) if Y=1.

Is this easily doable? I haven't worked with multiple ifs and nested ifs in half a decade & my brain is fried.

 

[bbott]

Basically, I need to know Z*AA if Y=0, and Z-(Z*AA%) if Y=1.

Is this what you're looking for?

=IF(Y1=0,Z1*AA1,IF(Y1=1,1-Z1*AA1,""))

or, if Y can only be 1 or 0:

=IF(Y1,1-Z1*AA1,Z1*AA1)

 

[legreenwood]

that would likely work except I failed to fully describe my problem (sorry! How can I hope for help if I don't fully describe?!!)

Team is designated in column X & whether they wrote the account (1) vs. lost it (0) is in column y. The designated team DID write the account the prior 2 years so the losses apply whether they wrote the account this year or not, but the loss control discount on losses only applies if they wrote the account this year, and losses only apply to the team designated in column X.

I need have in separate columns (so 1 solution for Team 1 will be modified to apply to Teams 2 & 3):
Team 1 losses (column Z) if they wrote it discounted by column AA(%); or if they lost it, then losses not discounted (column Z), and 0 value if they didn’t write it the prior 2 years (Team 1 is not designated team for that record)
Team 2…(same as above)
Team 3…(same as above)

Values in column Y will only be blank, 0 or 1

Thank you for replying!

 

[legreenwood]

I FINALLY FIGURED IT OUT!
I created a new column just calculating the adjusted losses,
=IF(y2=1,z2-(aa2*y2%),z2)
then applied whether the adjusted losses applied to the team in an if(and) statement:
=IF(B2=1,ab2,"") and so on for each team in its own column.

Just being there you guys helped take the pressure off so my brain could work. I'm sure this is playschool - but it's working so I'll run with it!