Formula for comparison of Three Teams

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 B1

Any help with the Formula in G1 would be very much appreciated.
TIA
Rashid Khan

Hi Rashid
I thought I sent you an example sheet for this some weeks ago?
Did you encounter problems with the suggested solution?

--
Regards
Frank Kabel
Frankfurt, Germany

"Rashid Khan" schrieb im Newsbeitrag
...
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 B1

Any help with the Formula in G1 would be very much appreciated.
TIA
Rashid Khan

Hi Frank,
I replied twice or thrice to you. But did not get a reply from u. I sent
you the worksheet also. Did u not get it?

The formula suggested by u did not work!
I have mailed u the worksheet.. Pls have a look.

Rashid Khan

"Frank Kabel" wrote in message
...
Hi Rashid
I thought I sent you an example sheet for this some weeks ago?
Did you encounter problems with the suggested solution?

--
Regards
Frank Kabel
Frankfurt, Germany

"Rashid Khan" schrieb im Newsbeitrag
...
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 B1

Any help with the Formula in G1 would be very much appreciated.
TIA
Rashid Khan

Hi
no I didn't get a sheet. Could be blocked by my filter / virus checker

--
Regards
Frank Kabel
Frankfurt, Germany

"Rashid Khan" schrieb im Newsbeitrag
...
Hi Frank,
I replied twice or thrice to you. But did not get a reply from u. I
sent
you the worksheet also. Did u not get it?

The formula suggested by u did not work!
I have mailed u the worksheet.. Pls have a look.

Rashid Khan

"Frank Kabel" wrote in message
...
Hi Rashid
I thought I sent you an example sheet for this some weeks ago?
Did you encounter problems with the suggested solution?

--
Regards
Frank Kabel
Frankfurt, Germany

"Rashid Khan" schrieb im Newsbeitrag
...
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 B1

Any help with the Formula in G1 would be very much appreciated.
TIA
Rashid Khan

Hi Frank,
Then how can I mail you the worksheet? Pls guide

Rashid
"Frank Kabel" wrote in message
...
Hi
no I didn't get a sheet. Could be blocked by my filter / virus checker

--
Regards
Frank Kabel
Frankfurt, Germany

"Rashid Khan" schrieb im Newsbeitrag
...
Hi Frank,
I replied twice or thrice to you. But did not get a reply from u. I
sent
you the worksheet also. Did u not get it?

The formula suggested by u did not work!
I have mailed u the worksheet.. Pls have a look.

Rashid Khan

"Frank Kabel" wrote in message
...
Hi Rashid
I thought I sent you an example sheet for this some weeks ago?
Did you encounter problems with the suggested solution?

--
Regards
Frank Kabel
Frankfurt, Germany

"Rashid Khan" schrieb im Newsbeitrag
...
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 B1

Any help with the Formula in G1 would be very much appreciated.
TIA
Rashid Khan

Hi
make sure it does not contain any macros and don't compress it.

--
Regards
Frank Kabel
Frankfurt, Germany


Rashid Khan wrote:
Hi Frank,
Then how can I mail you the worksheet? Pls guide

Rashid
"Frank Kabel" wrote in message
...
Hi
no I didn't get a sheet. Could be blocked by my filter / virus
checker

--
Regards
Frank Kabel
Frankfurt, Germany

"Rashid Khan" schrieb im Newsbeitrag
...
Hi Frank,
I replied twice or thrice to you. But did not get a reply from u.
I sent you the worksheet also. Did u not get it?

The formula suggested by u did not work!
I have mailed u the worksheet.. Pls have a look.

Rashid Khan

"Frank Kabel" wrote in message
...
Hi Rashid
I thought I sent you an example sheet for this some weeks ago?
Did you encounter problems with the suggested solution?

--
Regards
Frank Kabel
Frankfurt, Germany

"Rashid Khan" schrieb im Newsbeitrag
...
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 B1

Any help with the Formula in G1 would be very much appreciated.
TIA
Rashid Khan

Hi Frank,
I have sent the worksheet to u. It does not contain any macros.
The subject line is .........Worksheet for Formula help - Rashid Khan

Pls see if u get it this time.
Rashid Khan

"Frank Kabel" wrote in message
...
Hi
make sure it does not contain any macros and don't compress it.

--
Regards
Frank Kabel
Frankfurt, Germany


Rashid Khan wrote:
Hi Frank,
Then how can I mail you the worksheet? Pls guide

Rashid
"Frank Kabel" wrote in message
...
Hi
no I didn't get a sheet. Could be blocked by my filter / virus
checker

--
Regards
Frank Kabel
Frankfurt, Germany

"Rashid Khan" schrieb im Newsbeitrag
...
Hi Frank,
I replied twice or thrice to you. But did not get a reply from u.
I sent you the worksheet also. Did u not get it?

The formula suggested by u did not work!
I have mailed u the worksheet.. Pls have a look.

Rashid Khan

"Frank Kabel" wrote in message
...
Hi Rashid
I thought I sent you an example sheet for this some weeks ago?
Did you encounter problems with the suggested solution?

--
Regards
Frank Kabel
Frankfurt, Germany

"Rashid Khan" schrieb im Newsbeitrag
...
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 B1

Any help with the Formula in G1 would be very much appreciated.
TIA
Rashid Khan

Hi Rashid
just for future emails to me or other reulars in this Excel NG:
1. Your file send to me DOES contain macros. Opening your file Excel
issues a macro warning. And you HAVE macros in this file. e.g. in the
first module:
Option Explicit

' Function for conversion of a Currency to words
' Parameter - accept a Currency
' Returns the number in words format
'*************************************************

Function CurrencyToWord(ByVal MyNumber)
Dim Temp
Dim Rupees, Paisa As String
Dim DecimalPlace, iCount
Dim Hundreds, Words As String
ReDim Place(9) As String
'.....


2. Normally I would simply delete such files, esp. if the sender (you)
told me the file will not contain macros. Also this was the reason why
my filter blocked your prior files automatically (I just set you for
this email on my white list)

3. Also it would be nice if you had explained your manually calculated
values a little bit. Back Re-engineering is sometimes fun but in this
case some details in your file would have been helpful :-)

But as it's a sunday and I have some time to spare I opened your file
and added some formulas. File is on the way back to you


--
Regards
Frank Kabel
Frankfurt, Germany


Rashid Khan wrote:
Hi Frank,
I have sent the worksheet to u. It does not contain any macros.
The subject line is .........Worksheet for Formula help - Rashid
Khan

Pls see if u get it this time.
Rashid Khan

"Frank Kabel" wrote in message
...
Hi
make sure it does not contain any macros and don't compress it.

--
Regards
Frank Kabel
Frankfurt, Germany


Rashid Khan wrote:
Hi Frank,
Then how can I mail you the worksheet? Pls guide

Rashid
"Frank Kabel" wrote in message
...
Hi
no I didn't get a sheet. Could be blocked by my filter / virus
checker

--
Regards
Frank Kabel
Frankfurt, Germany

"Rashid Khan" schrieb im Newsbeitrag
...
Hi Frank,
I replied twice or thrice to you. But did not get a reply from
u.
I sent you the worksheet also. Did u not get it?

The formula suggested by u did not work!
I have mailed u the worksheet.. Pls have a look.

Rashid Khan

"Frank Kabel" wrote in message
...
Hi Rashid
I thought I sent you an example sheet for this some weeks ago?
Did you encounter problems with the suggested solution?

--
Regards
Frank Kabel
Frankfurt, Germany

"Rashid Khan" schrieb im Newsbeitrag
...
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 B1

Any help with the Formula in G1 would be very much appreciated.
TIA
Rashid Khan

Hi Frank,
Pls accept my sincere apologies. The formula works perfect.
Thanks for your help and time.
Rashid
"Frank Kabel" wrote in message
...
Hi Rashid
just for future emails to me or other reulars in this Excel NG:
1. Your file send to me DOES contain macros. Opening your file Excel
issues a macro warning. And you HAVE macros in this file. e.g. in the
first module:
Option Explicit

' Function for conversion of a Currency to words
' Parameter - accept a Currency
' Returns the number in words format
'*************************************************

Function CurrencyToWord(ByVal MyNumber)
Dim Temp
Dim Rupees, Paisa As String
Dim DecimalPlace, iCount
Dim Hundreds, Words As String
ReDim Place(9) As String
'.....


2. Normally I would simply delete such files, esp. if the sender (you)
told me the file will not contain macros. Also this was the reason why
my filter blocked your prior files automatically (I just set you for
this email on my white list)

3. Also it would be nice if you had explained your manually calculated
values a little bit. Back Re-engineering is sometimes fun but in this
case some details in your file would have been helpful :-)

But as it's a sunday and I have some time to spare I opened your file
and added some formulas. File is on the way back to you


--
Regards
Frank Kabel
Frankfurt, Germany


Rashid Khan wrote:
Hi Frank,
I have sent the worksheet to u. It does not contain any macros.
The subject line is .........Worksheet for Formula help - Rashid
Khan

Pls see if u get it this time.
Rashid Khan

"Frank Kabel" wrote in message
...
Hi
make sure it does not contain any macros and don't compress it.

--
Regards
Frank Kabel
Frankfurt, Germany


Rashid Khan wrote:
Hi Frank,
Then how can I mail you the worksheet? Pls guide

Rashid
"Frank Kabel" wrote in message
...
Hi
no I didn't get a sheet. Could be blocked by my filter / virus
checker

--
Regards
Frank Kabel
Frankfurt, Germany

"Rashid Khan" schrieb im Newsbeitrag
...
Hi Frank,
I replied twice or thrice to you. But did not get a reply from
u.
I sent you the worksheet also. Did u not get it?

The formula suggested by u did not work!
I have mailed u the worksheet.. Pls have a look.

Rashid Khan

"Frank Kabel" wrote in message
...
Hi Rashid
I thought I sent you an example sheet for this some weeks ago?
Did you encounter problems with the suggested solution?

--
Regards
Frank Kabel
Frankfurt, Germany

"Rashid Khan" schrieb im Newsbeitrag
...
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 B1

Any help with the Formula in G1 would be very much appreciated.
TIA
Rashid Khan