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,F11<E11),1,0) which gives the desired result that if F10 greater than E10 and F11 less than E11 count is 1.

How can I expand the formula over a range to give an answer in X32? =IF(AND(F10:F18>E10:E18,F11:F18<E11:E18),1,0) doesn't work as it is or as an array.Thanks

Rick

✔ Best Answer

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

inputto the code.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

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

ANDall 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?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03

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 F

6 7

5 6

4 3

6 6

6 7

5 6

5 6

5 6

4 3Hope that's a little clearer.

Regards

Rick

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 3Should the total for this data be

2?F11 is greater than E11 and F12 is less than E12

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

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03

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%Regards

Rick

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.

Regards

Rick

Hi DerbyDad03, I spoke too soon as it didn't work when other bogies were involved, so... I guess I'm back to square 1

RegardsRick

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

inputto the code.

Click Here Before Posting Data or VBA Code ---> How To Post Data or 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

Ask Your Question

Weekly Poll

Do you think ride and car sharing are the future of transportation?

Discuss in The Lounge

Poll History