help with IF function

[Krivo666]

Hi, I'm terrible with IF function, and therefore need bit of help

what I need to calculate is, if H2 is less than 1.5, and M2 is 1 then return value of 1, or if M2 is 0, then return 0,

I need to get this working for H2 values lover then 1.5, between 1.5 and 1.9, and more than 2.

If I get those values (0/1) returned I can calculate percentages easily.

any suggestions?

all help will be appreciated

 

[Giordano Bruno]

What do you want to happen if M2 = 1, but H2>=1.5?

 

[Oeldere]

I placed helpcollumn for your convience

The answer is in the green cell.

Excel Workbook
	D	E	F	G	H	I	J	K	L	M
1	H2	M2
2	0	1	0		1,50					1
Blad1

Cell Formulas
Range	Formula
D2	=IF(H2<1.5,1,0)
E2	=IF(M2=1,1,0)
F2	=IF(AND(H2<1.5,M2=1),1,0)

 

[Krivo666]

A	B	C	D	E	F
Odds	Won/Lost
1.865	0
1.733	1
1.644	1
1.380	0
2.051	0
	Percentage won

<tbody>
</tbody>

in column A I have list of odds, in columnd B: 1/0 for won/lost, what I need to calculate is percentage won for each band of odd (e.g 1.00-1.49, 1.5-2 and >2

so I supposes there needs to be two IFs, if A is <1.5 and is 1 then it returns 1, if it's 0 then return 0, but if it's more than 1.5 ignore it, than in column D would be 1.5-1.99, and would return 1, if B is 1 and A is between 1.5-1.99 etc.

hope you can understand what I'm trying to to, I'm not great in explaining these things

thanks for your help

 

[Oeldere]

Please give us an example with the desired result.

 

[Krivo666]

A	B	C	D	E	F
Odds	W/L	<1.5	1.5-199	>2
1.865	0		0
1.733	1		1
1.644	1		1
1.380	0	0
2.051	1			1
	Percentage won	0%	66%	100%

<tbody>
</tbody>

 

[Oeldere]

This one will do.

Excel Workbook
	A	B	C	D	E
14	Odds	W/L	1,5	1,5-1,99	2
15	1,865	0	0	0	0
16	1,733	1	0	1	0
17	1,644	1	0	1	0
18	1,38	0	0	0	0
19	2,051	1	0	0	1
Blad3

Cell Formulas
Range	Formula
C15	=IF(AND($A15),1,0)
C16	=IF(AND($A16),1,0)
C17	=IF(AND($A17),1,0)
C18	=IF(AND($A18),1,0)
C19	=IF(AND($A19),1,0)
D15	=IF(AND($A15>C$14,$A15),1,0)
D16	=IF(AND($A16>C$14,$A16),1,0)
D17	=IF(AND($A17>C$14,$A17),1,0)
D18	=IF(AND($A18>C$14,$A18),1,0)
D19	=IF(AND($A19>C$14,$A19),1,0)
E15	=IF(AND($A15>E$14,$B15=1),1,0)
E16	=IF(AND($A16>E$14,$B16=1),1,0)
E17	=IF(AND($A17>E$14,$B17=1),1,0)
E18	=IF(AND($A18>E$14,$B18=1),1,0)
E19	=IF(AND($A19>E$14,$B19=1),1,0)

 

[Krivo666]

works great, but could you tell me how to do average on each column then, because now there's value, in each column,

for example, I need to find out how many 1.5-1.99 are there, and then calculate how many were 1 or 0,

 

[Oeldere]

Maybe like this.

Excel Workbook
	B	C	D	E
22	sum	0	2	1
23	average	0,00%	66,67%	33,33%
Blad3

Cell Formulas
Range	Formula
C22	=SUM(C15:C19)
C23	=+C22/SUM($C$22:$E$22)
D22	=SUM(D15:D19)
D23	=+D22/SUM($C$22:$E$22)
E22	=SUM(E15:E19)
E23	=+E22/SUM($C$22:$E$22)

 

[Krivo666]

what I need to get is percentage from only one column,

A	B	C	D	E
Odds	W/L	<1.5	1.5-1.99	2+
1.865	0		0
1.733	1		1
1.644	1		1
1.380	0	0
2.051	1			1
		0%	66%	100%

<tbody>
</tbody>

so if there where 3 bets in range of 1.5-1-99, percentage of won is 66.66% (2 out of 3)

your formula calculates percentage of overall entries