Multiple IF AND statement not working.

[lambi153]

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;

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

 

[lambi153]

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]

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

 

[lambi153]

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]

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.

=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.