VLOOKUP and IF function problem

[Rashid Khan]

Hello All,
I am creating a worksheet as follows:

Col Headings
A = Team (can be either Y or Z)
B = Criteria (can be either A or B)
C = Points Gain
D = Points Loss
E = ScoreofY (Formula required)
F = ScoreofZ (Formula required)

I want to have a Lookup formula in Columns E and F as follows:

Formula For Column E:
LOOKUP the team in Column A and then the Criteria in Column B. IF the Team
is Y and Criteria is A then calculate Points Gain*Points Loss, and in
Column F show the value of Col C with a negative sign and
If the Team is Y and Criteria is B then calculate Points Gain*Points Loss
with a negative sign and show the value of Col C with a positive sign.

Formula For Column F:
LOOKUP the team in Column A and then the Criteria in Column B. IF the Team
is Z and Criteria is A then calculate Points Gain*Points Loss, and in Column
E show the value of Col C with a negative sign and
If the Team is Z and Criteria is B then calculate Points Gain*Points Loss
with a negative sign and show the value of Col C with a positive sign.

I think I have made myself clear.

Any help would be appreciated.

I would be entering only value in Column A, B, C and D... and the values in
Columns E and F should be calculated with a LOOKUP and IF formula... Any
other suggestions would also be greatly appreciated.

Have a nice weekend

R. Khan

 

[Frank Kabel]

Hi
try the following formulas:
E1:
=IF(A1="Y",C1*D1*(1-2*(B1="A")),"")

F1:
=IF(A1="Z",C1*D1*(1-2*(B1="A")),"")

copy both formulas down

 

[Rashid Khan]

Hi Frank,
Your formula works only for one condition and it does not test both the
possibilities.

I give an example of what is required. I am tring to give further
explanation. There can be four possibilities as follows. I have shown
formula required for each possibilities.

1) Team Y/Criteria A: E1 = +PointGain*PointLoss & F1 = -PointGain

2) Team Y/Criteria B: E1 = -PointGain*PointLoss & F1= +PointGain

3) Team Z/Criteria A: E1 = -PointGain & F1 = +PointGain*PointLoss

4) Team Z/Criteria B: E1 = +PointGain & F1 =-PointGain*PointLoss

I have shown positive and negative signs to make the matter clear. Please
remember that in one row there can be only Team Yor Team Z and similarly the
Criteria can be either A or B.

I hope it is clear to you now.
Thanks for your time and help

Rashid Khan

 

[Frank Kabel]

Hi
have you tried the formulas?
the part
*(1-2*(B1="A"))
is used to test this condition. e.g.
B1 = "A" then this part would result in
*(1-2*(1)) = *(-1)

if B1 <>"A" this part would result in
*(1-2*(0)) = *(1)

so the formula should work

 

[Rashid Khan]

Hi Frank.
Yes Frank.. I had copied the formula suggested by you. But it does not
work.

It shows the same amount ... one side positive and the other side negative..
What can be the problem.. If if u say I can mail u my worksheet to have a
look at

Nasir

 

[Frank Kabel]

Hi
mail me your worksheet

 

[Frank Kabel]

Hi
please never ATTACH a file to this NG. We agreed on mailing not on
attaching

The following formulas should do:
E1:
=IF(A1="Y",C1*D1*(1-2*(B1<>"A")),C1*(1-2*(B1="A")))

F1:
=IF(A1="Z",C1*D1*(1-2*(B1<>"A")),C1*(1-2*(B1="A")))

 

[Rashid Khan]

I am extremely sorry Frank. Thanks for the formula u have suggested.
I will revert back if any further problems are there.

Thanks for the time and help

 

[Rashid Khan]

Hello Frank.
I have a small problem now.
I have added another Criteria T (for Tie) and also added another
Column G as Tie

I need your help for a formula for Column G
If tie then ScoreY and ScoreZ should show PointsGain with positive
sign and Tie should show PointsGain * PointsLoss with a negative sign

In other words there will always be two value with positive sign and one
with the negative sign. Hope I have made it clear

Please help me out.

TIA

 

[Rashid Khan]

Hello Frank.
Sorry I did not explain the problem correctly.

I have added another Team X and I need to calculate the formula for the
Score of Team X in G.

Sorry for this

Rashid Khan