How do I perform operations within a funtion that operates on colu

I am trying to calculate the % of people under a certain age.  In the 
COUNTIF() funtion, how do I calculate the age and then compare it to the 
chosen value?
e.x.  =COUNTIF(A1:A3,(calculate age) <=60)

7/14/1932
4/28/1951
11/11/1939


0
crander8 (1)
11/29/2004 9:35:13 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
406 Views

Similar Articles

[PageSpeed] 39

COUNTIF has its limits. Here are 2 ways:

=SUMPRODUCT(--(YEAR(TODAY())-YEAR(A1:A3)<=60))
=SUMPRODUCT(--(DATEDIF(A1:A3,TODAY(),"y")<=60))

HTH
Jason
Atlanta, GA

>-----Original Message-----
>I am trying to calculate the % of people under a certain 
age.  In the 
>COUNTIF() funtion, how do I calculate the age and then 
compare it to the 
>chosen value?
>e.x.  =COUNTIF(A1:A3,(calculate age) <=60)
>
>7/14/1932
>4/28/1951
>11/11/1939
>
>
>.
>
0
Jason
11/29/2004 9:49:05 PM
THANKS

"Jason Morin" wrote:

> COUNTIF has its limits. Here are 2 ways:
> 
> =SUMPRODUCT(--(YEAR(TODAY())-YEAR(A1:A3)<=60))
> =SUMPRODUCT(--(DATEDIF(A1:A3,TODAY(),"y")<=60))
> 
> HTH
> Jason
> Atlanta, GA
> 
> >-----Original Message-----
> >I am trying to calculate the % of people under a certain 
> age.  In the 
> >COUNTIF() funtion, how do I calculate the age and then 
> compare it to the 
> >chosen value?
> >e.x.  =COUNTIF(A1:A3,(calculate age) <=60)
> >
> >7/14/1932
> >4/28/1951
> >11/11/1939
> >
> >
> >.
> >
> 
0
crander (1)
11/29/2004 10:13:02 PM
one way:

    =SUMPRODUCT(--(DATEDIF(A1:A3,TODAY(),"y")<=60))

for the usage of -- in SUMPRODUCT formulae, see

    http://www.mcgimpsey.com/excel/doubleneg.html



In article <69B40E06-4CD6-44E0-87E8-9B96B93544C1@microsoft.com>,
 "crander8" <crander8@discussions.microsoft.com> wrote:

> I am trying to calculate the % of people under a certain age.  In the 
> COUNTIF() funtion, how do I calculate the age and then compare it to the 
> chosen value?
> e.x.  =COUNTIF(A1:A3,(calculate age) <=60)
> 
> 7/14/1932
> 4/28/1951
> 11/11/1939
0
jemcgimpsey (6723)
11/29/2004 10:15:03 PM
Reply:

Similar Artilces:

vba macro to perform custom reporting -transpose from rows to columns
I have one workbook and two sheets 1 sheet has data for automated test scenarios that may be run up to 3 time if they don't pass on the first or second try Sheet1: TestSet Test Result reason if failed 1 pass 2 pass 3 pass 4 fail x 4 fail y 4 fail z I want to create a summary report that looks like Sheet2 for the Sheet1 logs above test run1 result test run2 result test run 3result Sheet2 Testset result1 result2 result3 1 pass 2 pass 3 pass 4 fail fail fail So if a testset is executed more ...

Illegal operation error while printing EXCEL or WORD Files
Hi, I am facing an illegal operation error when i try to print any file from excel (any no. of pages), this happens in stand alone printer as well as a networked printer. When we press the print button, it flashes this message, but still prints, but once the printing is completed, i will have to restart the PC. Due to this error other applications PRINTING also will NOT HAPPEN and the only way out is, restart the PC. This happens not only in EXCEL, it happens in all the MS applications (outlook, access, front page, powerpoint also). When I check the print manager (before restart),...

performances
performances Hi, I am writing c++ application in vc++ 6 the application use a lot of cpu processing (math calculations), I am running the application on Win-XP, p-4 2.8 HT! What is the best way to compile my application so it will run faster? What are the best ways to write or build my application for faster running? What i the different betwine processor type Blend* inn the C/C++ tab of the project setting and the other option??? Thanks! "Dave" <dbg@012.net.il> wrote in message news:eY8jUE38EHA.3756@TK2MSFTNGP14.phx.gbl... > performances > > Hi, > I am wr...

Business Contact Manager, performance sacrifices?
I'm hoping we can all learn a little about Business Contact Manager... After some initial difficulties that were resolved by 1.5 hours with Microsoft and some great ideas from Patricia Cardoza's new book, I've got Business Contact Manager running smoothly. That's a good thing because I provide support and training in Outlook to clients and want to be able to recommend this new add-on. But is it ready to recommend? Here are two things that seriously bother me: my contacts take so much longer to load and sort. Searching from the "Find a Contact" box formerly t...

KB for Performance Optimizer trouble
In the process of moving EDB files from a crashed server to a new server built with same name, etc, but different platform (from NT to W2K Sp4) I found the Performance Optimizer would not allow me to move files to different volumes. A quick google search showed because of SP4 I needed to contact Microsoft for the patch but when I called they said I needed the KB article and I searched and searched and even none of the 'MVP' replies saying 'contact microsoft' had the KB number. After having the MS rep hold the line while I was scouring google and retrying the process to get the ...

Workflow email: Lead converted within 24 hours
Hi, I'm having trouble figuring out how to make a timed workflow email based on whether a condition's been satisfied or not. I've tried tips from the "Using MS CRM" book as well as searches within this group. Here's what I'm going for and my progress: We need an email to be sent out when a Lead isn't converted within 24 hours of an Owner receiving it. I think 24 hours after creation would be acceptable to start with, but Ownership would be even better. The closest I can come is a workflow that says: When Lead Status is Changed Wait for 1 day after Le...

Perform function...if?
Hi I have a workbook that I'm continually adding data to daily. and I also have another workbook that I want to perform calculations, but I only want the calculations performed IF there is data in the first workbook... Here are the two formulas the way they are now... ='Stock Return Data'!J24*0.25 ='Stock Return Data'!J24-'Tax Data'!A14 How can I tell both these functions, to only perform if there is data on the original worksheet? Thanks, Confused Man Hi try =IF('Stock Return Data'!J24<>"",'Stock Return Data'!J24*0.25,"&...

Macros -- Why not implement them in Store Operations Manager?
Macros are apparently only intended to work in Store Operations POS. It would make initial data loading and setup much better is they worked in Store Operations Manager. Thanks Kent Smith ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Busines...

Is it possible to shade text within a text box?
I would like to be able to add shading to text within a text box. Is this possible? Thanks for your help. Format, font, select the shadow. The text is shadowed gray, that is the only way you can shadow text unless you use WordArt. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com http://officebeta.iponet.net/en-us/publisher/FX100649111033.aspx "Hair Traders" <HairTraders@discussions.microsoft.com> wrote in message news:A452279C-1C69-4235-9520-54D9CB993ADF@microsoft.com... >I would like to be able to add shading to ...

MATCH within INDEX question
Hello, In a previous thread I got a nice solution to a lookup problem I ha with the INDEX/MATCH functions. I would like to learn exactly what the formula does so I understand i completely rather than just copy it in the future. I understand all apects of the following formula except for one: What is the "1" for in "MATCH(1,"? Thank you for any responses. Ron Hekier (Portion from previous thread follows) --------------------------------------- Hi try the following array formula (entered with CTRL+SHIFT+ENTER): =INDEX('sheet2'!$E$1:$E$100,MATCH(1,('sheet2&#...

E2K3 Performance
My E2K3 server is having an occasional lag time in processing and would like some ideas on where to look at improving performance. 2.4 Xeon processor 1.3 Gb RAM Lot's of disk space Page Files are c: 1920 - 4096 and d: the same (seems incorrect, maybe should be static value?) When checking the running system, CPU is at 4% and pagefile is at 1.64 Gb. It's a low use server, less than 100 mailboxes and a backend to OWA out front. Suggestions appreciated. Thanks. ...

A Special Paste Funtion
I was once able to copy a lot of data that was contained in about 12 Columns and 2000 rows. I was then able to use a special paste function that automaticlly deleted duplicate records and pasted only the ones that were unique. Any Ideas? Thank you Jay You can use Advanced Filter to extract unique items from a list. There are instructions here: http://www.contextures.com/xladvfilter01.html#FilterUR Jay wrote: > I was once able to copy a lot of data that was contained > in about 12 Columns and 2000 rows. I was then able to use > a special paste function that automaticlly de...

iesample performance in CEPC is slow
I have created a CEPC by using Wince 6 and when i browse INTERNET the speed is very slow with compare to standard Windows 7 running X86 machine. (The test carried out in the same machine) . For both case i use the Internet Explorer to browse the net. Is there any method to improve the performance of IE sample browser. Huh? What hardware are you comparing to what hardware? You don't seriously expect a Windows 7 dual-core 3.3GHz processor running 4GB of RAM with a high-performance display chip/driver to be comparable to a 900MHz ATOM processor with 256MB of RAM and a VGA F...

avalon from within mfc ?
Hello, I am working for a bigger company that has a large win32 (mainly MFC) codebase. Their products mainly are audio- and multimedia authoring applications for the home user market (allthough there are some pro tools too ). Since most of those apps rely heavily on performance and they need to support older OSes too they don't see themselfes moving to .NET anytime soon. As Longhorn with Aero and Avalon emerges they are starting to get worried about if their win32/MFC applications will be able to benefit fromt the rich userexperience that Aero and Avalon promise. I think this a situiati...

Excel performance slowly
Dear All, We have a license for MS office XP professional but anybody have a problem like us. It's very slow in network when we use a icon tools bar or pooldown menu function. We upgrade our Office XP version to service pack 3 ready. Some computers in our factory have a problem like slowly but some have not. We does not know how to solve this problem. Best Regards, Songphan C. IT Manager MPO ASIA Co.,LTD. songphan@mpoasia.com Hi only some general ideas: - clear the temp directory, reboot and try again - virus scanner could cause this behaviour -- Regards Frank Kabel Frankfurt, ...

Perform
Hi; I want to implement a Special Upgrade against one of my company database (I have 16 company database) but I received the error while doing upgrade. I need to run Perform Special Upgrade because I found that one of the stored procedure is not there "glpCreatePostingActivity" on a new created company. I found a red "X" on the company selection in Great Plains Utility. How to fix? ...

Save operation on table MOP_Order_MSTR failed accesing SQL Data
I am getting the above message, when entering the MO entry window in V10, SP4 Any idea why? -- Doug Doug, that is a very generic error and won't tell anyone anything other than something didn't work when trying to read a MFG table (the MOP_Order_MSTR) table. If you press the "More Info" button your client will give you the SQL error that was returned. Review or post that error message and someone should be able to help you. patrick developer support PS v10.0 is only up to SP1 so far, not SP4. I doubt the version matters in this case but you never know. -- Thi...

Sending Messages within an Messagehandler Function
Hi everybody. I am doing the following: void CADlg::OnAutomatic() // called by the message handler { while(1) // it isn't an endless loop but it takes some time { // do something ... ((CEdit*)GetDlgItem (IDC_SUT_NUMBER))->SetReadOnly(TRUE); while((m_connectionStatus!=CONNECT_SUT || flow.GetMsgOut(0)!=0) && !m_StopAutotest ) { int breakOut=0; while(::PeekMessage (&message,NULL,0,0,PM_REMOVE) && breakOut++<200) { // enable message communication ::TranslateMessage(&...

questionable operation
I have a user with an installation of Windows 2000, Office 2000 with all the latest service packs. Opening Excel first and then opening a .XLS file works as designed. Opening anything else, either from a shortcut or the actual file ends up with not only that file open but a 'Book1' file open as well. Also being opened this way if any change is made to the original file and an attempt to Exit (not just close the file) is made...it asks the usual "Do you want to save changes" message...but then it does not close the file. Both 'Book1' and the original file remain open. ...

count the number of new lows within a range of cells
Hello, Is there a formula or method to count the number of new lows within a range of cells? For example, the following are in col A rows 5 to 14. Within this set a new low has been reached 3 times- (96,95,94). What I would like is a formula or macro that that would produce the results - 3. There are about 9000 rows of data and I am looking for "count of new lows within the preceding 10 rows" on a rolling basis. Thanks. 100 101 96 97 98 97 95 97 94 95 Version 1: Use this if the 9000 or so rows don't have blanks in them. In this version you just need to select the first cel...

query performance inconsistence
I put a query on two seperate query windows and both connect to the same SQL server. I ran it individually. One is running less than 3 secs and the other one is taking > 20 secs. The execution plan is a bit different. The obvious different one in the execution plan is that the one that is running faster is using parallism. The confusion part for me is that it is the same query running on the same server but on two different connections. Can this be possible? I am very puzzled. The server is running SQL 2005 sp3 std. od -- Message posted via SQLMonster.com http://...

Performance down
In Microsoft Outlook 2002, I tried the journalling and the performance went down drastically. Just to open or preview a single mail message of hardly 1 KB it consumes 4 to 5 seconds. The same situation continues even after removing the auto-journalling. I am totally frustrated as it takes 15 to 30 mins just to browse through 20 mails every morning. Can someone help me out to troubleshoot? Thanks in advance. ...

Re: Invalid Operation 437
We are using Microsoft Dynamics Retail Management System version 2.0, Point of Sale. In attempting to convert a quote to a sales order, we are getting the following error message: Invalid Operation 437: Invalid transaction type was detected Has anyone else come across this error? Suggestions or help are very appreciated. We're pretty much getting our feet wet with this and are very eager to use this. Thanks. ...

You dont have appropriate permission to perform this operation.
In Outlook 2003 when I click on send and recieve it gaves me following error message: "You dont have appropriate permission to perform this operation." Please help Try checking the properties of the following files: outlook.srs, outlook.dat and views.dat. These files are located in: c:\document & settings\<Username>\application data\microsoft\outlook see if those files are set to read-only and, if they are, remove the read-only attribute. Post back on the outcome. "Khalid" <Khalid@discussions.microsoft.com> wrote in message news:53719C3A-BBDC-47EE-...

Serialization performance
We're seeing long load (and save) times when serializing MFC object collections. As an example, a 6MB file is taking 30secs to load on a fast machine and we're wondering what sort of tricks were available to optimize this. Details are below. We're using an MFC typed ptr array, declared inside our collection class as shown below. >>> template<class T> class CMyCollection { public: CTypedPtrArray <CObArray, T*> array; }; <<< To load the collection, we're calling the Serialize method of the typed ptr array as shown below....