If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Help required with Formula
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 |
#2
|
|||
|
|||
Help required with Formula
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 Khan wrote: 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 |
#3
|
|||
|
|||
Help required with Formula
Hi Frank,
I have mailed u the worksheet. "Frank Kabel" wrote in message ... 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 Khan wrote: 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 |
Thread Tools | |
Display Modes | |
|
|