# help with IF function

#### Krivo666

##### New Member
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

### Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
What do you want to happen if M2 = 1, but H2>=1.5?

I placed helpcollumn for your convience

The answer is in the green cell.
Excel Workbook
DEFGHIJKLM
1H2M2
20101,501
Cell Formulas
RangeFormula
D2=IF(H2<1.5,1,0)
E2=IF(M2=1,1,0)
F2=IF(AND(H2<1.5,M2=1),1,0)

 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

Please give us an example with the desired result.

 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>

This one will do.
Excel Workbook
ABCDE
14OddsW/L1,51,5-1,992
151,8650000
161,7331010
171,6441010
181,380000
192,0511001
Cell Formulas
RangeFormula
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)

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,

Maybe like this.
Excel Workbook
BCDE
22sum021
23average0,00%66,67%33,33%
Cell Formulas
RangeFormula
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)

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

Replies
3
Views
127
Replies
5
Views
68
Replies
5
Views
159
Replies
4
Views
135
Replies
0
Views
290

### Forum statistics

1,203,625
Messages
6,056,393
Members
444,862
Latest member
more_resource23 ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back