Multiple IF AND statement not working.

lambi153

New Member
Joined
Mar 6, 2018
Messages
12
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
Joined
Mar 6, 2018
Messages
12
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
Joined
Mar 6, 2018
Messages
12
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
Joined
Mar 6, 2018
Messages
12
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.
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top