[Year 12 IPM] Excel problem

[Alex and Alison Hopkins]

Jock,

I can't quite imagine why anyone would want to count how many times "R"
appears in column A AND "F" appears in column B.

But I guess it's the principle of the thing.

You could try to combine a conditional formula with a FIND (or similar)
function, but it doesn't seem to cope when IF (FIND("A1", "R") > 0, ...
, ...) evaluates to zero.

So I reckon the best way is to write your own function.  The formula
itself is quite simple.  I've named it "two" because the point of this
exercise seems to be testing two conditions, but you can name it
anything you like (except for a few reserved words, I guess):

Private Function two(ByVal aref As String, ByVal fref As String) As
Integer
aref = UCase(aref)
fref = UCase(fref)

Dim condition1 As Boolean
If InStr(aref, "R") > 0 Then
   If InStr(fref, "F") > 0 Then
     two = 1
   Else
     two = 0
   End If
Else
  two = 0
End If

End Function


To use this, you have to enter it into a VBA code module (however it's
properly called).  At the risk of explaining what you may already know:
To do this, when you're in the spreadsheet, use the key combination
Alt-F11;  then the menu path, Insert>Module, then copy and paste the
formula in.  Then, say in column B, if you've left that free, type in
your new formula.  It works like any other Excel formula in that you use
the = key to enter it, and you can copy and paste the formula when
you've got the first one in.  It takes two parameters, the cell in the A
column and the cell in the F column, so the first row formula will be:
=two(A1,F1)

HTH,

Alex Hopkins
Bayside Christian College

PS I was working on this probably at the same time as Geoff Moss was
working on his.  I've interpreted as trying to search for the letters
within a string, rather than having just one character in each of the
two columns.





-----Original Message-----
From: ipm-bounces at edulists.com.au [mailto:ipm-bounces at edulists.com.au]
On Behalf Of Jock Garnsworthy
Sent: Wednesday, 29 March 2006 7:15 PM
To: ipm at edulists.com.au
Subject: [Year 12 IPM] Excel problem


Hi all, 

I wish to count the number of entries that meet two conditions.
Eg: Count how many times "R" appears in column A in the same row  as "F"
appears in column B.

I have tried to use the logical function "and" in conjunction with
"countif" but can't seem to make it work.

Anyone have a solution?

---
Cheers

Jock
__________________________

Jock Garnsworthy

Williamstown High School (Pasco Campus)
_______________________________________________
http://www.edulists.com.au - FAQ, resources, subscribe, unsubscribe IPM
Mailing List kindly supported by http://www.vcaa.vic.edu.au - Victorian
Curriculum and Assessment Authority and http://www.vitta.org.au  - VITTA
Victorian Information Technology Teachers Association Inc
-------------- next part --------------
A non-text attachment was scrubbed...
Name: RnF.xls
Type: application/vnd.ms-excel
Size: 23040 bytes
Desc: not available
Url : http://www.edulists.com.au/pipermail/ipm/attachments/20060329/fec4d999/RnF-0001.xls