I'm a newbie please help with my function

I have an excel spreadsheet that has 2 columns of numbers side by side.
I want a formula that counts the number of times the number in colum
one are larger than the number that is right beside it in column two.
After it counts it I want to display the total number that are large
in a seperate cell.  Is this possible.  I cruised around the forum an
I did not see what I was looking for so any help would be appreciated

--
colincanno
-----------------------------------------------------------------------
colincannon's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2610
View this thread: http://www.excelforum.com/showthread.php?threadid=39433

0
8/9/2005 7:53:22 PM
excel.newusers 15348 articles. 2 followers. Follow

3 Replies
682 Views

Similar Articles

[PageSpeed] 23

=SUMPRODUCT(--(A1:A100>B1:B100))

-- 

HTH

RP
(remove nothere from the email address if mailing direct)


"colincannon" <colincannon.1tijjd_1123618019.9967@excelforum-nospam.com>
wrote in message
news:colincannon.1tijjd_1123618019.9967@excelforum-nospam.com...
>
> I have an excel spreadsheet that has 2 columns of numbers side by side.
> I want a formula that counts the number of times the number in column
> one are larger than the number that is right beside it in column two.
> After it counts it I want to display the total number that are larger
> in a seperate cell.  Is this possible.  I cruised around the forum and
> I did not see what I was looking for so any help would be appreciated.
>
>
> -- 
> colincannon
> ------------------------------------------------------------------------
> colincannon's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=26105
> View this thread: http://www.excelforum.com/showthread.php?threadid=394335
>


0
bob.phillips1 (6510)
8/9/2005 8:11:30 PM
colincannon Wrote:
> I have an excel spreadsheet that has 2 columns of numbers side by side.
> I want a formula that counts the number of times the number in colum
> one are larger than the number that is right beside it in column two.
> After it counts it I want to display the total number that are large
> in a seperate cell.  Is this possible.  I cruised around the forum an
> I did not see what I was looking for so any help would be appreciated.

ASSUME for this exercise that your numbers are in Cells A2:A100 an
Cells B2:B100.

Based on the above assumption, here is a very simple solution (I a
sure that there is a more elegant solution out there).

In Cell C2, enter this formula (without the quotes) "=A2-B2" and cop
down until Cell C100. We will call Column C (Cells C2 to C100) as 
helper column. 

In cell C1, enter this formula:   =countif(C2:C100,">0") and this wil
count the "number of times the number in column one are larger than th
number that is right beside it in column two."

To display the numbers, you can enter this formula in Cell D2

=if(C2>0,A2,"")

and copy down until Cell D100.

Hope this is what you are looking for.

Regards

--
BenjieLo

-----------------------------------------------------------------------
BenjieLop's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1101
View this thread: http://www.excelforum.com/showthread.php?threadid=39433

0
8/9/2005 8:34:29 PM
=SUM(--(A1:A100>B1:B100))




I got a lot of my begginer help on this site...  cool 
They teach you by making a, Time sheet, Invoice and cheek book from
scratch on Windows media Player very cool 

www.MDOTutorials.com


-- 
Route2391
------------------------------------------------------------------------
Route2391's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26774
View this thread: http://www.excelforum.com/showthread.php?threadid=394335

0
8/30/2005 1:11:10 AM
Reply:

Similar Artilces:

Data Validation Help Needed urgently !!!!!!!!!!!
I am trying to write a data validation in VBA. I know I have the code right but I can't get it to work. It works fine if the validation was done dirctly in excel but I get an error when I try the same thing in VBA. Below is a snippet of the code I am having a problem with. The problem seem to be with the " Formula1:=" line. I have been scratching my head for the past 24hrs now trying to figure this out and I am still not coming up with anything. Any help will be greatly appreciated. Range("B3:C3").Select With Selection.Validation .Delete .Add Type...

Help with Calling a Module within a Module
Simple problem, but I'm confused, I'm trying to call a module "TextToData" within the following code, but its coming up with a compile error. My code (I thought would work) is. Info on how to fix would be great (I know the individual parts work) Sub GenerateReport() Sheets("Sales Mix").Select Cells.Select Selection.ClearContents Range("A1").Select Selection.QueryTable.Refresh BackgroundQuery:=False Range("A1").Select Application.ScreenUpdating = False With Application .Calculation = xlManual .Max...

cell watch function in excel 2001 for mac?
is there a cell watch window in excel 2001 for mac? if not is there way to download an add-in for it -- mike1 ----------------------------------------------------------------------- mike10's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1441 View this thread: http://www.excelforum.com/showthread.php?threadid=26193 In article <mike10.1cxcny@excelforum-nospam.com>, mike10 <mike10.1cxcny@excelforum-nospam.com> wrote: > is there a cell watch window in excel 2001 for mac? if not is there a > way to download an add-in for it? MacXL's VBE do...

IMS Out Queues grows in 100s/ per second, Can You help Pls ?
My Internet Mail Service Out-bound mails grows in several hundreds per seconds, in Exchange 5.5 sp4. My boss will soon fire me. Please is there any help out there ? Your help will be appreciated . thanks Atai Atai, Well, after spending the better part of two weeks on this issue. Here's what I found out: (1) This is referred to as a NDR Reverse Spam Attack (2) There are no settings within 5.5 that will stop this attack. (3) In talking with Microsoft, they recommended a 3rd party spam filtering software (4) There only a few companies that have software that specifically have th...

newbie question on finding and editing
Using vb.net, I need to search through XML files to locate specific elements and once found, update attributes of their companion elements. In plain language, I need to find specific book titles and update note information about each book. Not every book would be updated; just selected ones. So, I am confused whether to use DOM, XPath, both, or something else? My understandig of Xpathnavigator is that it is a read-only class. And DOM seems good for mass updates, but how to use it to find things? Thanks for any advise! george Hi Geoge, I'd suggest you use Xml DOM and XmlElement. ...

Help with Excel 2007
I am using Excel 2007. Each time I Close Excel I get an error box (Microsoft Office Excel has encountered a problem and needs to close. We are sorry for the inconvenience) and if I don't un-check "restart microsof office" it will restart Excel. What can this problem be? Hi Rick Start Excel Office button > Excel options Click on Resources And run Microsoft Office Diagnostics -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Rick Price" <jayg359@gmail.com> wrote in message news:xnLli.48661$mZ7.14728@fe01.news.easynews.com... >I am using Exc...

Average Function
Hi group, I have a formula in a cell that takes the average Sales of the 12 rows (for Jan to Dec) like so: =Average(A1:A12)The problem is that for example, since therea are no sales figures from August to Dec, the average is underestimated because it averages it over the entire range of cells (though they are zero from Augus to Dec). How can I make this Average function to be smarter and only average those months that the sales is not zero (August to Dec is Not BLANK but its zero) Hi Average is a very simple thing: SUM/COUNT. So =SUM(A1:A12)/12 should do for all years with 12 mont...

Excel Writeback functionality with SSAS
Hi, I am working on Excel 2010 writeback functionality with SSAS. Writeback functionality works when the cube is of small size. Writeback functionality fails when the cube is of have size say 50lakh records. I get an error as Data cannot be retrieved from external data source. Error Message written by the external data source Server: The operation has been cancelled. ...

Exact Function
I'm trying to compare two columns of check numbers using the exact function to get a true or false. One column of check numbers have zeros in front of check number and second column does not have zeros, but the function is resulting in a false due to it comparing the two as text and not number. I've tried formatting the column to number but the zeros don't come off, any ideas??? EXACT is a text function so it does what it is supposed to do. if your check numbers are true excel numbers then you can compare them as A1=B1 Another possible way =EXACT(TEXT(A1,"...

INTEGRATION MGR HELP
I am using Great Plains 7.5 and Integration Manager 7.5. I have created a new integration for revenue and have added a source from an AS/400 file. I have added the General Journal as the destination and have mapped the destination fields that I need. I saved it and ran it and keep coming up with this error: Message DOC 1 ERROR: You must provide a message. Source Ln 317 Col 2, Update module, Last CBOM field read was root.'Note', Last window field visited was 'OK Button' of window Can someone please tell me what I need to do to get past this. thanks so much You hv...

Help with Message Filtering & BCC
I'm sorry if my orginal post wasn't clear enough, so I'll try to elaborate. A.adds cc or bcc automatic settings options I would like to set up a bcc to an email address that automatically does so when I send a message. With Outlook 07, I have to do it manually each time. Whereas with Thunderbird, you can preselect whom you want to bcc messages to. Each time you send a message it does it automatically. B. manages the message filter options something like Mozilla Thunderbird I'm having a real problem managing my message filters using Outlook. I have many folders representing ...

Arrgh!..Need OW Help Please
Let me preface by saying I am using this document http://www.msexchange.org/tutorials/OWA_Exchange_Server_2003.html as a guide. I have not configured any SSL options yet because I can't get it to work. Trying to build Exchange 2003 Frontend/backend on my LAN. Front end is W2k3 SP1\Exchange 2003 SP1 and Backend is W2k3 SP1\Exchange 2003 (no SP1). After installing Exchange on the 2nd server (to be the new Front-end), I am able to create a mailbox in it's own store and access it via OWA from a Win2000 Pro workstation. This mailbox resides on the 2nd server's own store. I then en...

Help-Error validating multiple documents
Hello. I've got a total of 5 schemas. They are: 1 - 1.xsd - a description of a city 2 - 2.xsd - a description of a town 3 - CityHall.xsd - a description of a cityhall-meant to be part of a document based on 1.xsd in "user area". 4 - TownHall.xsd - a description of a townhall-meant to be part of a document based on 2.xsd in "user area". 5 - testmultischema.xsd - a document which combines in a batch instances of cities and towns based upon 1.xsd and 2.xsd. I get the following xml validation error: ERROR: The 'http://adamExt.org:TownHall' element is not declar...

Urgent help: change outlook default conatct form
Hi all, We added several fields to contacts in the standard form (design mode). We want this form to become users' default conatct form. Also, old contacts are able to launch from this new customized form. are these possible? exchange 2003/outllokk2003/outlook 2007) Yes. This is possible. You need to change the form on the folder properties and then run a program to change the IPM form used on existing items. See the last two sections at http://www.outlook-tips.net/beginner/publish_form.htm for details and link to the program. -- Diane Poremsky [MVP - Outlook] ...

Help. Wrong home mta for all users
Gang, using adsiedit I have found that all of my users have the incorrect home mta: It looks like this: CN=Microsoft MTA\ DEL:fa6b4c63-7cdb-43c0-aa14-fb19c4e855b5,CN=Deleted Objects,CN=Configuration,DC=Health,DC=org Is there a way that I can change this for all users at once? Script/csv/ldf/admodify.net -- Bharat Suneja MVP - Exchange www.zenprise.com NEW blog location: www.exchangepedia.com/blog ---------------------------------------------- <troute_kilgore@yahoo.com> wrote in message news:1169148159.997905.48020@m58g2000cwm.googlegroups.com... > Gang, using adsiedit I have...

SMTP queuing help
Hi all, I have an exchange 2000 server on domain1. I am running exchange 2003 on a totally unrelated domain2. I would like to configure domain2 as a backup for domain1. What I mean by backup is just a place for the mail for to be queued up incase the Internet goes down for domain1. Users do not need access to it. Can anyone point me in the right direction? Thanks! -LT Hello Luniz, Thanks for your posting here. You may use the one of the following options to download messages from the backup server. Option 1: Configure SMTP connector with ETRN/TURN enabled ========== To use this m...

how do i set an auto date function in excel or word?
I would like all documnts in both word and excel to automatically date as the document or report is prepared. =NOW() "hpkcommish" wrote: > I would like all documnts in both word and excel to automatically date as the > document or report is prepared. for excell Go to View> Header & footer >custom header footer then click on the clock pic & the date pic (choose 1st if you want it in the left middle or right feild) For word basically the same Cheers peterm "hpkcommish" wrote: > I would like all documnts in both word and excel to automatica...

help on conditional
Here is what I have, =SUM(IF(Sheet1!D9="JF",Sheet1!E9,0)+IF(Sheet1!D10="JF",Sheet1!E10,0)+IF(Sheet1!D11="JF",Sheet1!E11,0)+IF(Sheet1!D12="JF",Sheet1!E12,0)+IF(Sheet1!D13="JF",Sheet1!E13,0)+IF(Sheet1!D14="JF",Sheet1!E14,0)+IF(Sheet1!D15="JF",Sheet1!E15,0)+IF(Sheet1!D16="JF",Sheet1!E16,0)*IF(Sheet1!D17="JF",Sheet1!E17,0)+IF(Sheet1!D18="JF",Sheet1!E18,0)+IF(Sheet1!D19="JF",Sheet1!E19,0)+IF(Sheet1!D20="JF",Sheet1!E20,0)+IF(Sheet1!D21="JF",Sheet1!E21,0)+IF(Sheet1!D22=&q...

Insert function
The Insert Ribbon will not open,just the Header/Footer portion will. I can not select Lines I have put in my document, to adjust them. Why?? You say Insert "Ribbon". Are you using Excel 2007? Maybe the sheet is protected. -- -Brigette "Kevin" wrote: > The Insert Ribbon will not open,just the Header/Footer portion will. I can > not select Lines I have put in my document, to adjust them. > Why?? ...

Help with If and Then statement
I have a form based on a query which is itself based on 2 tables tbllevy and tblindividual. The form has a control, txtpaidbybusiness, whose control source is a YES/NO field in tbllevy with the same name. The form has another control whose source is txtbusinessname in tbllevy. If txtpaidbybusiness is YES I want to populate the control txtbusinessname with the value of the field with the same name from tblindividual. The two tables are linked in the query by txtmemnumber.tblindividual and txtmemnbr.tbllevy Can someone help with the If Then statement I need to put in the AfterUpdate event...

Need helps about importing and etc.
I'm a newbie, where can i find information about RMS Setup and Deployment Overview, Installation Requirements, Terminology, Upgrading, Configuration, Import/Export, Limitations, and so on? thanks, Jay K Partner Source http://www.microsoft.com/BusinessSolutions/partnersource.mspx -- Mobitech Lady Amy Luby Mobitech 402.330.0707 www.mobitechonline.com "JayK" <JayK@discussions.microsoft.com> wrote in message news:3AD41CE8-6B86-45A0-A2EA-A61A91CFB482@microsoft.com... > I'm a newbie, where can i find information about RMS Setup and Deployment > Overview, Instal...

Can someone help me??? (0/1)
Let me start off by saying that I am no where near an excel expert so please bear with me. I have a schedule for my employees which is attatched to this post as an html file. They are contained in three different sheets within a workbook. Each sheet has its own formulas and such but I have excluded them and also condensed them down to save repitition. The 3 scheduling sheets are named Servers, Hostess, and BarBus I also have/want 7 other sheets named Mon thru Sun and a template is attatched as an html file as well. What I need to get working is the Mon thru Sun sheets to automatically pu...

wmp usage rights and virus's please help!!
Please help I am not very technical but have managed in the past to download tv shows and watch them using vuze and wmp. However now when i try and watch anything, I get a box open up and it says that I have to download media usage rights aquisition. When ever I click on start and download this it just gives me loads of malware and virus's. Mirar, virtumonde and win32.agent. How can I watch these programmes that I have downloaded without getting these. Fortunately spybot sorts them out for me, but I would dearly love to just be able to watch stuff that I have downloaded, a...

Combine an And OR function
I have a little function that i need help with. =IF(AND($B9="Z",AE9=35,J9=1),1000). This function works fine, however I need to add in somehow that J9 could equal M or 1 or C and I would like the function still to be true and return the value as 1000. As I see it, this is smoehow combining and AND OR statement but I cant get it to work. Thanks Try this: =3DIF(AND($B9=3D"Z",AE9=3D35,OR(J9=3D1,J9=3D"M",J9=3D"C")),1000,"not define= d") This will retern "not defined" if the conditions are not met. Hope this helps. ...

Table Help
Hello Everyone, I know I have seen this problem before and that I have corrected it in the past, but for some reason I can't figure it out this time or find something similar online. The problem is simply, when I view a certain table one of its columns is completely filled in with "##########". The data is still there because when I click on it the data appears, and the data is visible in a justified form. It can't stay like this but I can't figure how I did it in the first place or how to change it back. Any help would be greatly appreciated. Thank You Ko...