Windows Software

if conditional question in excel

•

Join Date: Apr 2004

Posts: 573

Reputation: Dark_Omen is an unknown quantity at this point

Solved Threads: 6

Dark_Omen

Offline

Posting Pro

if conditional question in excel

Aug 25th, 2006

I am trying to find out how many numbers in a column are above a certain number, so a column has 2 4 3 6 3, and I want to have a tally after the last row with how many are above 3, could this be done with an if conditional and how would it be done?
Thanks

Last edited by Dark_Omen; Aug 25th, 2006 at 9:53 pm. Reason: forgot to put that it was in excel

•

Join Date: Dec 2004

Posts: 2,413

Reputation: Comatose is a jewel in the rough

Solved Threads: 211

Comatose

Offline

Taboo Programmer

Re: if conditional question in excel

Aug 26th, 2006

Hmn, can this be a macro?

•

Join Date: Apr 2004

Posts: 573

Reputation: Dark_Omen is an unknown quantity at this point

Solved Threads: 6

Dark_Omen

Offline

Posting Pro

Re: if conditional question in excel

Aug 27th, 2006

yeah I guess I could do it, are you talking about a VB macro, and if so how would you do that?
Thanks

•

Join Date: Dec 2004

Posts: 2,413

Reputation: Comatose is a jewel in the rough

Solved Threads: 211

Comatose

Offline

Taboo Programmer

Re: if conditional question in excel

Aug 27th, 2006

Yes a VB Macro. Click Tools, Macro, Visual Basic Editor. Then click the Insert Menu, and Choose "Module". Then paste this into the module:

Help with Code Tags

(Toggle Plain Text)

Public Sub ColumnCountAbove()
' /* ************************************************ */
' /* SubRoutine To Tally The Total Number Of Columns  */
' /* Which Have Values Greater Than A Specified Value */
' /* ************************************************ */

' /* Declare The Variables That We Plan To Use */
Dim ActiveColumn
Dim AllAbove
Dim TotalAbove
Dim I

' /* Get The Currently Active Column */
ActiveColumn = Excel.ActiveCell.Column

' /* Retrieve Information From The User, Regarding The Control Number */
' /* (That is, The Number From Which We Compare All Others To) */
AllAbove = InputBox("Above What Number?")

' /* If The User Enters Nothing, Just Quit */
If AllAbove = vbNullString Then Exit Sub

' /* Set Our Counter Variable To 1 */
' /* This Variable Keeps Track Of Which Row We Are In */
' /* And Which Row We Are Going To */
I = 1

' /* Since We Haven't Started Comparing Anything */
' /* The Total Tally Of How Many Are Above The Said */
' /* Numbers Should Be Zero... */
TotalAbove = 0

' /* Loop Until We Reach A Blank A Cell */
Do Until Cells(I, ActiveColumn) = vbNullString
    ' /* If The Value Of The Cell Is Greater Than The Value The User Gave */
    ' /* Then Add 1 To Our Tally */
    If Val(Cells(I, ActiveColumn).Value) > Val(AllAbove) Then TotalAbove = TotalAbove + 1
    
    ' /* Move To The Next Row Down */
    I = I + 1
Loop

' /* Stick The Tally Total In The First Empty Row, Same Column */
Cells(I, ActiveColumn).Value = TotalAbove

End Sub

Then should you want to, Click In The (Name) Portion in the properties box on the left, and give it a name (other than module1). I called mine: ColumnCntAbove. Then just above that, you'll see some folders and things. Right Click what should be the only module there (either with the name you gave it, or module1) and export it. Find a good place, where it will be easy to access, and export it there. This way, you can add the procedure any time you need it, by simply opening the visual basic editor, and going to the file menu, and import file.

I had written this as an excel addin (.xla file), but it doesn't work the way I wanted it to (When you went to run the macro, you had to actually type the name of the function, instead of it just being in the list). There is one flaw to this sub, and that is that if there is a blank row in the middle of the column somewhere, it will stop the tally at the first blank entry in the column. If this is a problem, AND the number of blank rows in each column that will be tallied is always the same (for example, row 5, in every column will always be blank) then we can bypass this problem.... otherwise, it seems like we are stuck with no blank lines.

Let me know how this function turns out and works for you. If you need, I can make a bunch of different subs that are like the one above (for tallying rows, or for greater than or equal to (as opposed to just greater than), etc, etc).

Last edited by Comatose; Aug 27th, 2006 at 11:08 pm.