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


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

Report •


✔ Best Answer
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.

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

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

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 F
6 7
5 6
4 3
6 6
6 7
5 6
5 6
5 6
4 3

Hope 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 E12
F17 is greater than E17 and F18 is less than E18

The 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


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%	

Regards

Rick


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.

Regards

Rick


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

Rick


Report •

#7
June 25, 2014 at 08:18:31
✔ 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 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.

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


Report •

#8
June 25, 2014 at 20:58:02
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.
Regards

Rick


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


Report •

Ask Question