Formula if win or lose

[cdpaul]

Hi,
I can do very basic sums but I'm trying to create a sheet that would give the result depending on whether the result cell is "Win" or "Lose".

I've attached a screenshot and what I'm looking for is:
M3 to be E3*F3 if L3 is Win or -E3 if Lose.
One further thing if possible if L3 is blank then M3 is blank ie M3 looks empty unless/until Win or Lose is entered.

Much appreciate any help

Thanks
Paul

 

[Peter_SSs]

Like this?

BTW, I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

20 09 10.xlsm
	E	F	G	H	I	J	K	L	M
3	10	2.05						Win	20.50
4	10	2.24						Win	22.40
5	10	2.01						Lose	-10.00
6	10	2.13						Lose	-10.00
7
Win lose

Cell Formulas
Range	Formula
M3:M7	M3	=IF(L3="","",IF(L3="Win",E3*F3,-E3))

 

[ebea]

Another option are this, which exist as a ready made Sheet on the internet. Og hvis du er Svensker, så er skemaet lavet på Dansk.
I'm not sure, if I'm allowed to insert a Link, but I take the risk: https://oddsekspert.dk/odds-regnskab-skabelon/

Odds-Skabelon1.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1
2		Økonomi			Statistik
3		Indskud	kr 85,00		Vundet	1
4		Profit	kr 89,25		Tabt	0
5		Gen. Profit	kr 89,25		Antal	1
6		ROI %	105%		Gns. Odds	2,05
7
8			Dato	Samlet Odds	Indskud	Gevinst	Profit	Resultat
9			09-09-2020	2,05	85,00 kr.	174,25 kr.	89,25 kr.	W
10						- kr.	- kr.
11						- kr.	- kr.
12						- kr.	- kr.
13						- kr.	- kr.
14						- kr.	- kr.
15						- kr.	- kr.
Betting

Cell Formulas
Range	Formula
C3	C3	=SUM(E9:E305)
C4	C4	=SUM(G9:G305)
C5	C5	=C3*C6/F5
C6	C6	=C4/C3
F3	F3	=COUNTIF(H9:H305,"=W")
F4	F4	=COUNTIF(H9:H305,"=L")
F5	F5	=F3+F4
F6	F6	=SUM(D9:D67)/F5
F9:F15	F9	=D9*E9
G9:G15	G9	=IF(H9="W",F9-E9,IF(H9="L",-E9,IF(H9=3,0,0)))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C4,G9:G305	Cell Value	<0	text	NO
C4,G9:G305	Cell Value	>0	text	NO
H9:H305	Cell Value	="L"	text	NO
H9:H305	Cell Value	="W"	text	NO

 

[cdpaul]

Like this?

BTW, I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

20 09 10.xlsm
	E	F	G	H	I	J	K	L	M
3	10	2.05						Win	20.50
4	10	2.24						Win	22.40
5	10	2.01						Lose	-10.00
6	10	2.13						Lose	-10.00
7
Win lose

Cell Formulas
Range	Formula
M3:M7	M3	=IF(L3="","",IF(L3="Win",E3*F3,-E3))

Thank so much, that's perfect and I take on board your suggestion

Paul

 

[cdpaul]

Another option are this, which exist as a ready made Sheet on the internet. Og hvis du er Svensker, så er skemaet lavet på Dansk.
I'm not sure, if I'm allowed to insert a Link, but I take the risk: https://oddsekspert.dk/odds-regnskab-skabelon/

Odds-Skabelon1.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1
2		Økonomi			Statistik
3		Indskud	kr 85,00		Vundet	1
4		Profit	kr 89,25		Tabt	0
5		Gen. Profit	kr 89,25		Antal	1
6		ROI %	105%		Gns. Odds	2,05
7
8			Dato	Samlet Odds	Indskud	Gevinst	Profit	Resultat
9			09-09-2020	2,05	85,00 kr.	174,25 kr.	89,25 kr.	W
10						- kr.	- kr.
11						- kr.	- kr.
12						- kr.	- kr.
13						- kr.	- kr.
14						- kr.	- kr.
15						- kr.	- kr.
Betting

Cell Formulas
Range	Formula
C3	C3	=SUM(E9:E305)
C4	C4	=SUM(G9:G305)
C5	C5	=C3*C6/F5
C6	C6	=C4/C3
F3	F3	=COUNTIF(H9:H305,"=W")
F4	F4	=COUNTIF(H9:H305,"=L")
F5	F5	=F3+F4
F6	F6	=SUM(D9:D67)/F5
F9:F15	F9	=D9*E9
G9:G15	G9	=IF(H9="W",F9-E9,IF(H9="L",-E9,IF(H9=3,0,0)))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C4,G9:G305	Cell Value	<0	text	NO
C4,G9:G305	Cell Value	>0	text	NO
H9:H305	Cell Value	="L"	text	NO
H9:H305	Cell Value	="W"	text	NO

Thank you, it could certainly have been an option but for the reply from Peter

 

[Peter_SSs]

Thank so much, that's perfect ...

You're welcome. Thanks for the follow-up.


BTW, that template has a very odd formula in column G
=IF(H9="W",F9-E9,IF(H9="L",-E9,IF(H9=3,0,0)))
a) Why would H9 be 3 (or 9 or "z",...)?
b) That final IF function will return 0 for any value of H9!

 

[ebea]

You're welcome. Thanks for the follow-up.


BTW, that template has a very odd formula in column G
=IF(H9="W",F9-E9,IF(H9="L",-E9,IF(H9=3,0,0)))
a) Why would H9 be 3 (or 9 or "z",...)?
b) That final IF function will return 0 for any value of H9!

Yes, it looks strange, with the rest of the IF formula. The last section (you marked in Red) should be removed. The person who made the Template, must have had something else in mind, with the Formula!

 

[Peter_SSs]

The last section (you marked in Red) should be removed.

.. or replaced with "" so that the formula returns nothing until W/L is entered in column H .. at which point it becomes basically identical to my suggested formula, just written in a different order.