# Multiple IF AND statement not working.

#### lambi153

##### New Member
I've taken on the role of organizing the weekly golf score for a group of guys. We have an excel sheet with some basic formulas and I wanted to take it to the next stage. I am trying to put in multiple IF and AND formula into the cells, but as I get towards fitting them all in it ends in "value".

As the weekly scores are inputted in cells c18-c27, c28 shows the average.

At the same time in a corresponding cell C32 I want it to do the following

• If the value of C18<30 then a value of 0.2 is returned
• If the value of C18(greater than or equal to)30 but <c28 a="" value="" of="" 0.1="" is="" returned.<="" li=""></c28>
• If the value of C18>36 and >C28 a value of ((C18-round(C28,0))*-0.1))-(C18-36))*0.1)) is returned. For example if the person scores 37 points and the average is 36, their handicap will drop by 0.1 for every point over the average and an extra 0.1 for every point over 36.
• if the value of C(less than or equal to)36 and >C28 a value of ((C18-round(c28,0))*-0.1)) is returned. I.e if the average is 34 and the player scores 36 points then a score of -0.2 is returned.
• This formula would then pulled down to the rest of the cells in the table.

The formula I'm trying in the first cell is as below but as I'm a novice I'm losing my way;

Code:
``=IF(C18<30,0.2),IF(AND((C18[U]>[/U]30),(C18<c28),0.1),if(and((c18>36),(C18>C28),((C18-ROUND(C28,0))*-0.1)-(C18-36))*0.1)),IF(AND((C18[U]<[/U]36[U]),[/U](C18>C28),((C18-ROUND(C28,0))*-0.1)))(``

Any help would be most appreciated.</c28),0.1),if(and((c18>

### Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

#### lambi153

##### New Member
I just realised that the second point wasn't finished. It should be if C18>30 and <C28 a value of 0.1 is returned. I didn't get that in the code and i also ended it with a ( instead of )

#### lambi153

##### New Member
C18>30 and <C28 a value of 0.1 is returned.

#### lambi153

##### New Member
C18>30 AND <C28 A VALUE OF 0.1 IS RETURNED. I'm not sure why bits of my posts are going missing!!
I hope I'm not asking too much for the formula for each cell?

#### lambi153

##### New Member
I've taken on the role of organizing the weekly golf score for a group of guys. We have an excel sheet with some basic formulas and I wanted to take it to the next stage. I am trying to put in multiple IF and AND formula into the cells, but as I get towards fitting them all in it ends in "value".

As the weekly scores are inputted in cells c18-c27, c28 shows the average.

At the same time in a corresponding cell C32 I want it to do the following

• If the value of C18<30 then a value of 0.2 is returned
• If the value of C18(greater than or equal to)30 but <c28 a="" value="" of="" 0.1="" is="" returned.<="" li=""></c28>
• If the value of C18>36 and >C28 a value of ((C18-round(C28,0))*-0.1))-(C18-36))*0.1)) is returned. For example if the person scores 37 points and the average is 36, their handicap will drop by 0.1 for every point over the average and an extra 0.1 for every point over 36.
• if the value of C(less than or equal to)36 and >C28 a value of ((C18-round(c28,0))*-0.1)) is returned. I.e if the average is 34 and the player scores 36 points then a score of -0.2 is returned.
• This formula would then pulled down to the rest of the cells in the table.

The formula I'm trying in the first cell is as below but as I'm a novice I'm losing my way;

<c28),0.1),if(and((c18>The code i now have does everything apart from point 3. As soon as I put 37 points into Cell C18 it comes up with false.

Code:
``=IF(AND(C18>36,C18>C28,((C18-ROUND(C28,0))*-0.1)-0.1*(C18-36)),IF(C18=ROUND(C28,0),"",IF(AND(C18<37,C18>C28),((C18-ROUND(C28,0))*-0.1),IF(C18<30,0.2,IF(AND(C18>29,C18<C28),0.1)))))``

Any help would be most appreciated.</c28),0.1),if(and((c18>

See the latest code above.

Replies
8
Views
190
Replies
4
Views
94
Replies
8
Views
793
Replies
3
Views
537
Replies
4
Views
103

1,130,403
Messages
5,641,936
Members
417,247
Latest member
Chitaah

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