Help required with Formula

  • Thread starter Thread starter Rashid Khan
  • Start date Start date
R

Rashid Khan

Hello All,
I am creating a worksheet as follows:

Col Headings
A1 = Team (can be either Y or Z)
B1 = Criteria (can be either A or B)
C1 = Points Gain
D1 = Points Loss
E1 = ScoreofY (Formula required)
F1 = ScoreofZ (Formula required)



The following formula was suggest by Mr. Frank Kabel for E and F as per the
requirement given below:

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



Formula suggested by Mr. Frank Kabel

E2 =IF(A1="Y",C1*D1*(1-2*(B1<>"A")),C1*(1-2*(B1="A")))

F2 =IF(A1="Z",C1*D1*(1-2*(B1<>"A")),C1*(1-2*(B1="A")))Both the above formula
works fine for my requirement. I have added another team in A1 (which can
be either X, Y or Z) and a new column G1 as ScoreofX.I need to calculate the
Formula in G1. One value will always be negative and two values will be
positive depending on the Criteria of B1Any help with the Formula in G1
would be very much appreciated.TIARashid Khan
 
Hi
if you like could you email an example sheet with your expected results
for each condition and I'll have a look at it
email: frank[dot]kabel[at]freenet[dot]de
 
Hi Frank,
I have mailed u the worksheet.

Frank Kabel said:
Hi
if you like could you email an example sheet with your expected results
for each condition and I'll have a look at it
email: frank[dot]kabel[at]freenet[dot]de


--
Regards
Frank Kabel
Frankfurt, Germany


Rashid said:
Hello All,
I am creating a worksheet as follows:

Col Headings
A1 = Team (can be either Y or Z)
B1 = Criteria (can be either A or B)
C1 = Points Gain
D1 = Points Loss
E1 = ScoreofY (Formula required)
F1 = ScoreofZ (Formula required)



The following formula was suggest by Mr. Frank Kabel for E and F as
per the requirement given below:

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



Formula suggested by Mr. Frank Kabel

E2 =IF(A1="Y",C1*D1*(1-2*(B1<>"A")),C1*(1-2*(B1="A")))

F2 =IF(A1="Z",C1*D1*(1-2*(B1<>"A")),C1*(1-2*(B1="A")))Both the above
formula works fine for my requirement. I have added another team in
A1 (which can be either X, Y or Z) and a new column G1 as ScoreofX.I
need to calculate the Formula in G1. One value will always be
negative and two values will be positive depending on the Criteria of
B1Any help with the Formula in G1 would be very much
appreciated.TIARashid Khan
Click to expand...
Click to expand...
 
Back
Top