Solved

Selective comparison to count

Posted on 2012-03-19
5
305 Views
Last Modified: 2012-03-19
This is a follow up to a previously answered question.

{=SUM(($BH$3:$BH$507>=BQ3:BQ507)*1)}

I am using the above formula to evaluate and compare two columns of numbers.

Column range BQ3:BQ507 has the ability to contain 0.  Is it possible to use the above formula, or something similar, to evaluate only those cells which have a number greater then 0?

Thanks for your input.
0
Comment
Question by:MCaliebe
  • 3
  • 2
5 Comments
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 250 total points
Comment Utility
{=SUM((BQ3:BQ507<>0)*($BH$3:$BH$507>=BQ3:BQ507)*1)}
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
Comment Utility
I am not sure but I think you can get rid of *1 for this case

{=SUM((BQ3:BQ507<>0)*($BH$3:$BH$507>=BQ3:BQ507))}
0
 

Author Closing Comment

by:MCaliebe
Comment Utility
I didn't try the other example...the initial worked fine.

Now, I just need to get schooled on what an "Array" formula is and I need to use them.  Any suggetions?
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
Comment Utility
To understand this start with a small range say

{=SUM((BQ3:BQ7<>0)*($BH$3:$BH$7>=BQ3:BQ7))}

then

Formulas > Formula auditing > Evaluate formula

Step through this by clicking the Evaluate button and see how the formula works.
0
 

Author Comment

by:MCaliebe
Comment Utility
Thanks!  I am following this.  In addition, I found a good article here http://office.microsoft.com/en-us/excel-help/introducing-array-formulas-in-excel-HA001087290.aspx on how and why we use array formulas.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now