Formula for comparison of Three Teams

[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 B1

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

 

[Frank Kabel]

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

 

[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]

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

 

[Rashid Khan]

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

Rashid

 

[Frank Kabel]

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

 

[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]

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

 

[Rashid Khan]

Hi Frank,
Pls accept my sincere apologies. The formula works perfect.
Thanks for your help and time.
Rashid