# Solved IF and AND in a range, Excel 2003

June 24, 2014 at 01:16:09
Specs: windows 7
 I am trying to define whether F10 is > E10. If it is and the cell below,F11 is less than E11 then 1 is the answer. It should only count if the equation is true. I have this formula =IF(AND(F10>E10,F11E10:E18,F11:F18

See More: IF and AND in a range, Excel 2003

June 25, 2014 at 08:18:31
 Would you be willing to use a User Defined Function (UDF)? A UDF is a function written in VBA, similar to a macro, but is entered into a cell just like an Excel built-in function. The drawback is that macros need to be enabled on all systems that will use the function. Enabling macros is a manual process since using a macro to enable macros would be a huge security risk. In other words, whoever has IT responsibility for the systems has to agree to enable macros and manually take that step.As an example, this UDF seems to accomplish your goal, but needs to be tested with your data as you make changes to the data to ensure it consistantly returns the correct results.```Function BounceBack(ByVal myRange As Range) Application.Volatile 'Loop through Range, searching for Bogies 'Followed by Birdies For cnt = 1 To myRange.Cells.Count Step 2 If myRange.Cells(cnt + 1) > myRange.Cells(cnt) And _ myRange.Cells(cnt + 3) < myRange.Cells(cnt + 2) Then 'Increment Bounceback count when found BounceBack_count = BounceBack_count + 1 End If Next 'Loop through Range, counting Bogeys For cnt = 1 To myRange.Cells.Count Step 2 If myRange.Cells(cnt + 1) > myRange.Cells(cnt) Then Bogey_count = Bogey_count + 1 End If Next 'Calculate BounceBack rate BounceBack = BounceBack_count / Bogey_count End Function ```To use the UDF, you would open the VBA editor for the workbook, Insert a Standard Module and paste the code into the pane that opens. Then, in any cell in the spreadsheet you would enter the following, based on the range of the example data given:=BounceBack(E11:F19)The range can be changed just like any built-in function since the UDF uses the range chosen by the user. In other words, the range is not built into the code, it accepts the range as input to the code.

#1
June 24, 2014 at 12:34:05
 I'm confused.It sounds like you want to see a 1 in X32 if all values in F10:F18 are greater than their corresponding values in E10:E18 AND all values in F11:F18 are less than their corresponding values in E11:E18.Those 2 conditions cannot be met.In other words, if F11 is greater than E11, then F11 can't be less than E11 at the same time.One of us is missing something. Could you explain what you are trying to do in a little more detail?message edited by DerbyDad03

Report •

#2
June 24, 2014 at 15:27:56
 Hi DerbyDad03,This is all to do with golf stats. Finding where there is a Bogey followed by a Birdie.Below are the two columns of part of the data. What I'm trying to achieve is to define where column F is greater than column E, Bogey; followed immediately where column F is less than column E, Birdie. The total is then shown in X32.The second part is to then define how many bogies were made against how many bounce back Birdies negated the Bogie.E F6 75 64 36 66 75 65 65 64 3Hope that's a little clearer.Regards Rick

Report •

#3
June 24, 2014 at 17:30:30
 Well, that's a little clearer, although I'm still not sure what you mean by the "The total is then shown in X32.". The total of what? How many times a bogie is followed by a birdie?Let's look at your data. You didn't include row numbers, so I will add them...``` E F 10 6 7 11 5 6 12 4 3 13 6 6 14 6 7 15 5 6 16 5 6 17 5 6 18 4 3```Should the total for this data be 2?F11 is greater than E11 and F12 is less than E12F17 is greater than E17 and F18 is less than E18The second part is even less clear:"The second part is to then define how many bogies were made against how many bounce back Birdies negated the Bogie."I think you missed a word in there someplace. Please try that one again and give us the answer, based on the example data given, explaining it like I tried to do above. In other words, give us the answer and then tell us why that answer fits the data.message edited by DerbyDad03

Report •

Related Solutions

#4
June 24, 2014 at 20:20:33
 Hi DerbyDad03,Yes, for this example the total is 2, as shown below.The total of Bogies is 3 of which only 2 Birdies came directly after them.The calculation is 2/3 (Birdies/Bogies) which = 66%.The 66% is the answer that I am trying to formulate for cell X32.Does that explain it any clearer?``` E F Birdie Bogey 10 6 8 Bogey 1 11 5 6 Bogey 1 12 4 3 Birdie 1 13 6 6 14 6 6 15 5 5 16 5 7 Bogey 1 17 5 4 Birdie 1 18 4 4 Totals 2 3 Birdie Bounceback = 2/3 =66% ```RegardsRick

Report •

#5
June 24, 2014 at 22:39:28
 Hi DerbyDad03,Well, after displaying the data in my last post it got me thinking on a different track. I had used SUMPRODUCT() as an array before in my spreadsheet and I started experimenting with it.{=SUMPRODUCT(--(\$F\$10:\$F\$18>(\$E\$10:\$E\$18)))} sums the Bogies{=SUMPRODUCT(--(\$F\$10:\$F\$18<(\$E\$10:\$E\$18)))} sums the Birdies{=SUMPRODUCT(--(\$F\$10:\$F\$18<(\$E\$10:\$E\$18)))/SUMPRODUCT(--(\$F\$10:\$F\$18>(\$E\$10:\$E\$18))))}I formatted cell X32 as a percentage and the answer was 67%, which is correct.I thankyou for your time and patience. If I hadn't had to break it down to explain it to you, I wouldn't have been able to look at it differently.RegardsRick

Report •

#6
June 24, 2014 at 23:13:34
 Hi DerbyDad03,I spoke too soon as it didn't work when other bogies were involved, so... I guess I'm back to square 1RegardsRick

Report •

#7
June 25, 2014 at 08:18:31
 Would you be willing to use a User Defined Function (UDF)? A UDF is a function written in VBA, similar to a macro, but is entered into a cell just like an Excel built-in function. The drawback is that macros need to be enabled on all systems that will use the function. Enabling macros is a manual process since using a macro to enable macros would be a huge security risk. In other words, whoever has IT responsibility for the systems has to agree to enable macros and manually take that step.As an example, this UDF seems to accomplish your goal, but needs to be tested with your data as you make changes to the data to ensure it consistantly returns the correct results.```Function BounceBack(ByVal myRange As Range) Application.Volatile 'Loop through Range, searching for Bogies 'Followed by Birdies For cnt = 1 To myRange.Cells.Count Step 2 If myRange.Cells(cnt + 1) > myRange.Cells(cnt) And _ myRange.Cells(cnt + 3) < myRange.Cells(cnt + 2) Then 'Increment Bounceback count when found BounceBack_count = BounceBack_count + 1 End If Next 'Loop through Range, counting Bogeys For cnt = 1 To myRange.Cells.Count Step 2 If myRange.Cells(cnt + 1) > myRange.Cells(cnt) Then Bogey_count = Bogey_count + 1 End If Next 'Calculate BounceBack rate BounceBack = BounceBack_count / Bogey_count End Function ```To use the UDF, you would open the VBA editor for the workbook, Insert a Standard Module and paste the code into the pane that opens. Then, in any cell in the spreadsheet you would enter the following, based on the range of the example data given:=BounceBack(E11:F19)The range can be changed just like any built-in function since the UDF uses the range chosen by the user. In other words, the range is not built into the code, it accepts the range as input to the code.
 Hi DerbyDad03,Your UDF worked just fine. I understood the concept and modified it as below.myRange is E10:F18 (front nine holes) and myRange2 is E20:F28 (back nine holes).Calculations are all correct.Thankyou once again for all your time and patience, I really appreciate everything.RegardsRick```Function BounceBack(ByVal myRange, myRange2 As Range) Application.Volatile 'Loop through Range, searching for Bogies 'Followed by Birdies For cnt = 1 To myRange.Cells.Count Step 2 If myRange.Cells(cnt + 1) > myRange.Cells(cnt) And _ myRange.Cells(cnt + 3) < myRange.Cells(cnt + 2) Then 'Increment Bounceback count when found BounceBack1_count = BounceBack1_count + 1 End If Next For cnt1 = 1 To myRange2.Cells.Count Step 2 If myRange2.Cells(cnt1 + 1) > myRange2.Cells(cnt1) And _ myRange2.Cells(cnt1 + 3) < myRange2.Cells(cnt1 + 2) Then 'Increment Bounceback count when found BounceBack2_count = BounceBack2_count + 1 End If BounceBack_count = BounceBack1_count + BounceBack2_count Next 'Loop through Range, counting Bogeys For cnt = 1 To myRange.Cells.Count Step 2 If myRange.Cells(cnt + 1) > myRange.Cells(cnt) Then Bogey1_count = Bogey1_count + 1 End If Next 'Loop through Range, counting Bogeys For cnt1 = 1 To myRange2.Cells.Count Step 2 If myRange2.Cells(cnt1 + 1) > myRange2.Cells(cnt1) Then Bogey_count2 = Bogey_count2 + 1 End If Bogey_count = Bogey1_count + Bogey_count2 Next 'Calculate BounceBack rate BounceBack = BounceBack_count / Bogey_count End Function ```