Average using Sumproduct

Using Excel 2003, I need to find the average age of males who have had a 
specified procedure before March 31, 2010. D3:D11 are ages. E3:E11 are 
gender. F3:F11 are the dates. G3:G11 are the procedures. So far I have this 
formula but am getting a total of the ages rather than the average.
=AVERAGE((SUMPRODUCT(('East 2010'!$D$3:$D$11)*('East 
2010'!$G$3:$G$11="THR")*('East 2010'!$E$3:$E$11="Male")*('East 
2010'!$F$3:$F$11<=DATE(2010,3,31)))))

Any suggestions would be appreciated. Thank you.
0
Utf
4/12/2010 8:36:01 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

4 Replies
763 Views

Similar Articles

[PageSpeed] 33

"Basenji" wrote:
> Using Excel 2003, I need to find the average
> age of males who have had a specified procedure
> before March 31, 2010. D3:D11 are ages.
> E3:E11 are gender. F3:F11 are the dates.
> G3:G11 are the procedures. 

SUMPRODUCT is great when you want a single value, such as the total that you 
are getting.

But for AVERAGE, you want the argument to be an array or list of values.  
Try the follow array formula [*]:

=AVERAGE(IF(('East 2010'!$G$3:$G$11="THR")
*('East 2010'!$E$3:$E$11="Male")
*('East 2010'!$F$3:$F$11<=DATE(2010,3,31)),
'East 2010'!$D$3:$D$11))

[*] Enter an array formula by pressing ctrl+alt+Enter instead of just Enter. 
 In the Formula Bar, you should curly braces around the entire formula, viz. 
{=formula}.  Note that you cannot enter the curly braces yourself; that is 
just Excel's way of denoting an array formula when it is displayed.  If you 
make a mistake, select the cell, press F2, edit as needed, then press 
ctrl+alt+Enter.


----- original message -----

"Basenji" wrote:
> Using Excel 2003, I need to find the average age of males who have had a 
> specified procedure before March 31, 2010. D3:D11 are ages. E3:E11 are 
> gender. F3:F11 are the dates. G3:G11 are the procedures. So far I have this 
> formula but am getting a total of the ages rather than the average.
> =AVERAGE((SUMPRODUCT(('East 2010'!$D$3:$D$11)*('East 
> 2010'!$G$3:$G$11="THR")*('East 2010'!$E$3:$E$11="Male")*('East 
> 2010'!$F$3:$F$11<=DATE(2010,3,31)))))
> 
> Any suggestions would be appreciated. Thank you.
0
Utf
4/12/2010 9:15:01 PM
Hi,

Try this

=SUMPRODUCT(('East 2010'!$D$3:$D$11)*('East 2010'!$G$3:$G$11="THR")*('East 
2010'!$E$3:$E$11="Male")*('East 
2010'!$F$3:$F$11<=DATE(2010,3,31)))/SUMPRODUCT(('East 
2010'!$G$3:$G$11="THR")*('East 2010'!$E$3:$E$11="Male")*('East 
2010'!$F$3:$F$11<=DATE(2010,3,31)))

-- 
Regards,

Ashish Mathur
Microsoft Excel MVP

"Basenji" <Basenji@discussions.microsoft.com> wrote in message 
news:3B279D59-21BF-4D57-A26D-F9FA78C9AC6B@microsoft.com...
> Using Excel 2003, I need to find the average age of males who have had a
> specified procedure before March 31, 2010. D3:D11 are ages. E3:E11 are
> gender. F3:F11 are the dates. G3:G11 are the procedures. So far I have 
> this
> formula but am getting a total of the ages rather than the average.
> =AVERAGE((SUMPRODUCT(('East 2010'!$D$3:$D$11)*('East
> 2010'!$G$3:$G$11="THR")*('East 2010'!$E$3:$E$11="Male")*('East
> 2010'!$F$3:$F$11<=DATE(2010,3,31)))))
>
> Any suggestions would be appreciated. Thank you. 

0
Ashish
4/13/2010 12:16:43 AM
Thank you for the explanation between using sumproduct and average with 
array. Is the purpose of the asterisks to indicate multiple if criteria 
within the array? Also, is it possible to include ISERROR in the formula so 
that if there are no males that a blank cell or zero is returned instead of a 
divide by zero error message. I have tried several places but have been 
unsuccessful.

Thank you.

"Joe User" wrote:

> "Basenji" wrote:
> > Using Excel 2003, I need to find the average
> > age of males who have had a specified procedure
> > before March 31, 2010. D3:D11 are ages.
> > E3:E11 are gender. F3:F11 are the dates.
> > G3:G11 are the procedures. 
> 
> SUMPRODUCT is great when you want a single value, such as the total that you 
> are getting.
> 
> But for AVERAGE, you want the argument to be an array or list of values.  
> Try the follow array formula [*]:
> 
> =AVERAGE(IF(('East 2010'!$G$3:$G$11="THR")
> *('East 2010'!$E$3:$E$11="Male")
> *('East 2010'!$F$3:$F$11<=DATE(2010,3,31)),
> 'East 2010'!$D$3:$D$11))
> 
> [*] Enter an array formula by pressing ctrl+alt+Enter instead of just Enter. 
>  In the Formula Bar, you should curly braces around the entire formula, viz. 
> {=formula}.  Note that you cannot enter the curly braces yourself; that is 
> just Excel's way of denoting an array formula when it is displayed.  If you 
> make a mistake, select the cell, press F2, edit as needed, then press 
> ctrl+alt+Enter.
> 
> 
> ----- original message -----
> 
> "Basenji" wrote:
> > Using Excel 2003, I need to find the average age of males who have had a 
> > specified procedure before March 31, 2010. D3:D11 are ages. E3:E11 are 
> > gender. F3:F11 are the dates. G3:G11 are the procedures. So far I have this 
> > formula but am getting a total of the ages rather than the average.
> > =AVERAGE((SUMPRODUCT(('East 2010'!$D$3:$D$11)*('East 
> > 2010'!$G$3:$G$11="THR")*('East 2010'!$E$3:$E$11="Male")*('East 
> > 2010'!$F$3:$F$11<=DATE(2010,3,31)))))
> > 
> > Any suggestions would be appreciated. Thank you.
0
Utf
4/13/2010 1:09:01 PM
"Basenji" wrote:
> Is the purpose of the asterisks to indicate multiple
> if criteria within the array?

More specifically, the "*" (multiplication) functions as AND in this 
context, just as it does in SUMPRODUCT.  We cannot use AND for this purpose 
in an array formula.


> is it possible to include ISERROR in the formula so 
> that if there are no males that a blank cell or zero is
> returned instead of a divide by zero error message.

It is "possible", but it is messy.  You would have to repeat the entire 
formula.

If you were using Excel 2007, you could use IFERROR for a compact solution.  
But since you are using Excel 2003, you might use the following array formua 
[*]:

=IF(COUNTIF('East 2010'!$E$3:$E$11,"Male")=0, 0,
AVERAGE(IF(('East 2010'!$G$3:$G$11="THR")
*('East 2010'!$E$3:$E$11="Male")
*('East 2010'!$F$3:$F$11<=DATE(2010,3,31)),
'East 2010'!$D$3:$D$11)))

However, I think you really should test whether there are zero cells that 
meet __all__ of the required conditions, not just zero males.  So use the 
following array formula [*]:

=IF(SUMPRODUCT(('East 2010'!$G$3:$G$11="THR")
*('East 2010'!$E$3:$E$11="Male")
*('East 2010'!$F$3:$F$11<=DATE(2010,3,31)))=0, 0,
AVERAGE(IF(('East 2010'!$G$3:$G$11="THR")
*('East 2010'!$E$3:$E$11="Male")
*('East 2010'!$F$3:$F$11<=DATE(2010,3,31)),
'East 2010'!$D$3:$D$11)))

[*] Recall that you enter an array formula by pressing ctrl+alt+Enter 
instead of just Enter.  In the Formula Bar, you should curly braces around 
the entire formula, viz. {=formula}.  Note that you cannot enter the curly 
braces yourself; that is just Excel's way of denoting an array formula when 
it is displayed.  If you make a mistake, select the cell, press F2, edit as 
needed, then press ctrl+alt+Enter.


----- original message -----

"Basenji" wrote:
> Thank you for the explanation between using sumproduct and average with 
> array. Is the purpose of the asterisks to indicate multiple if criteria 
> within the array? Also, is it possible to include ISERROR in the formula so 
> that if there are no males that a blank cell or zero is returned instead of a 
> divide by zero error message. I have tried several places but have been 
> unsuccessful.
> 
> Thank you.
> 
> "Joe User" wrote:
> 
> > "Basenji" wrote:
> > > Using Excel 2003, I need to find the average
> > > age of males who have had a specified procedure
> > > before March 31, 2010. D3:D11 are ages.
> > > E3:E11 are gender. F3:F11 are the dates.
> > > G3:G11 are the procedures. 
> > 
> > SUMPRODUCT is great when you want a single value, such as the total that you 
> > are getting.
> > 
> > But for AVERAGE, you want the argument to be an array or list of values.  
> > Try the follow array formula [*]:
> > 
> > =AVERAGE(IF(('East 2010'!$G$3:$G$11="THR")
> > *('East 2010'!$E$3:$E$11="Male")
> > *('East 2010'!$F$3:$F$11<=DATE(2010,3,31)),
> > 'East 2010'!$D$3:$D$11))
> > 
> > [*] Enter an array formula by pressing ctrl+alt+Enter instead of just Enter. 
> >  In the Formula Bar, you should curly braces around the entire formula, viz. 
> > {=formula}.  Note that you cannot enter the curly braces yourself; that is 
> > just Excel's way of denoting an array formula when it is displayed.  If you 
> > make a mistake, select the cell, press F2, edit as needed, then press 
> > ctrl+alt+Enter.
> > 
> > 
> > ----- original message -----
> > 
> > "Basenji" wrote:
> > > Using Excel 2003, I need to find the average age of males who have had a 
> > > specified procedure before March 31, 2010. D3:D11 are ages. E3:E11 are 
> > > gender. F3:F11 are the dates. G3:G11 are the procedures. So far I have this 
> > > formula but am getting a total of the ages rather than the average.
> > > =AVERAGE((SUMPRODUCT(('East 2010'!$D$3:$D$11)*('East 
> > > 2010'!$G$3:$G$11="THR")*('East 2010'!$E$3:$E$11="Male")*('East 
> > > 2010'!$F$3:$F$11<=DATE(2010,3,31)))))
> > > 
> > > Any suggestions would be appreciated. Thank you.
0
Utf
4/13/2010 5:33:01 PM
Reply:

Similar Artilces:

Averaging numbers but ignoring < and
I have a column of data as illustrated below than contains numbers, blank cells, dashes and < entries. Is it possible to average only the numbers ensuring that the divsor is the number of cells that contain number entreis rather than all cells containing an entry (eg for below example answer should be 0.137) 0.3 <0.001 0.01 <0.01 0.1 Thank you On Wed, 23 Feb 2005 06:29:03 -0800, KIM <KIM@discussions.microsoft.com> wrote: >I have a column of data as illustrated below than contains numbers, blank >cells, dashes and < entries. Is it possible to average only ...

Average of Averages
Another seemingly simple process which I can't figure. :) I have a list of averages, and I want to get the average of them. As such, A 1 25% 2 42% 3 100% 4 50% I need a formula to average something like the above. Thanks, Alan =AVERAGE(A1:A4) is one possible solution. Hope it helps. -- goober ------------------------------------------------------------------------ goober's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=19838 View this thread: http://www.excelforum.com/showthread.php?threadid=513937 Hi Alan It really depen...

averaging less than values
How do I average a column of numbers where less than values are calculated as that number? 2 <2 3 4 <2 5 In the average I want the<2 to = 2. So the answer should be 3 -- JD Assuming source data as posted in A2:A7 Paste this in say, B2, then press CTRL+SHIFT+ENTER to array-enter the formula: =AVERAGE(SUBSTITUTE(A2:A7,"<","")+0) Adapt the ranges to suit -- Max Singapore http://savefile.com/projects/236895 Downloads:17,500 Files:358 Subscribers:55 xdemechanik --- "JD" wrote: > How do I average ...

xslt: making use of edit/delete buttons is that actually work?
I want to make an xslt file that formats and displays data from an XElement object through an xml control. I need to have the xslt file show edit/remove buttons next to each element/attribute that is displayed on the page. These buttons/links do have to actually work in the sense that you can actually edit/remove the particular element/attribute. Is there any way to do something like this? "Andy B" <a_borka@sbcglobal.net> wrote in message news:uKoP9jvuIHA.4476@TK2MSFTNGP06.phx.gbl... >I want to make an xslt file that formats and displays data from an XElement >...

Problem using xml writer
Hi I want to create following xml document using xmlwriter <?xml version="1.0" ?> - <epps> - <remit_trn type="technical"> <remit_date_dtm dtm = "201" >test</remit_date_dtm> <rmt_payee_n1>test</rmt_payee_n1> <rmt_payee_n1>test</rmt_payee_n1> <rmt_ref>test</rmt_ref> </remit_trn> </epps> I wrote the code to do the same but when I run the code I am getting following exception on executing the following line xmlw.WriteAttributeString("dtm01" , "201&quo...

Averages
Hello again, I have an interesting situation. I basically inherited an older database that is used to enter dates, usage and charges for accounts. The situation is that we are attempting to do averages filtered by account, and averaged against the number of days that data has been entered. So, for example, we had 7 months of data entered, our query should total the number of days in the seven months (x), then average that with the total usage number (y). This data currently resides in the same table labeled "data" in the form of from and to dates (6/1/1999 to 7/1/1999) and usage (32...

MFC application using properysheet and property page do not pop up when navigating between propertypages
Hi, I created a modeless propertysheet, i have added propertypages, when the application is launched the dialog appears on the screen, when i select any page and launch a browser so that the my application is hidden behind the launched browser, now wheen i click on the application in task bar tha dialog is not shown on the dialog.. can anyone help me in this regard. ...

Using contacts in new mails
Hi, I have added a number of contact groups or distribution lists. However when I create a new mail, and try to use the lists they are not found. This also applies to all my contacts. I seem to remember a setting that instructs Outlook 2000 where to look for email addresses etc but I can not find it now. Should I be able to create a distrbution list in my contacts folder called "Users" and then when creating a mail just write Users in the "To" field? And should I be able to include a distribution list in another distribution list? /Richard You mentioned yo...

is there a way that pocket excel can use drop down lists
is there a way that pocket excel can use drop down lists the same that i use to input data on the PC I don't think so. Pocket Excel is extremely limited. -- Jim Rech Excel MVP "Gary" <Gary@discussions.microsoft.com> wrote in message news:3DF40941-EB5F-4BF7-BE53-E63311933715@microsoft.com... | is there a way that pocket excel can use drop down lists the same that i use | to input data on the PC ...

how do I use incredimail in outlook 2003
can I use Incredimail 2.0 in Outlook in Office 2003. Windows 7 OS "bobbrenton" <bobbrenton@discussions.microsoft.com> wrote in message news:D67AC212-F57F-4DD2-9ABD-E716536FA68F@microsoft.com... > can I use Incredimail 2.0 in Outlook in Office 2003. Windows 7 OS You can't. You can, by devious means, export your messages and finally after some convoluted procedures, import them into Outlook. ...

Bug using CMap?
I can't seem to fix this bug I'm getting. I'm trying to populate a simple tree control in a dialog. When I double-click on an item, another dialog pops up to edit the item's properties. During these operations, I sometimes get a heap error: HEAP[SWP.exe]: HEAP: Free Heap block 14d078 modified at 14d0ec after it was freed which doesn't tell me much. After playing with this for a LONG time, I suspect (though I don't know) that the problem is related to my use of a CMap to populate the tree. The tree has one level of nodes representing material categories, something like...

Average Lead Time
Does anybody know if the average lead time in Item Vendor Maintenance counts weekends, or only business days? The average lead time is calcualted as follows: Enter the average number of days that pass between the time you place an order with this vendor and the time you receive the order. If you're using Purchase Order Processing, this field will be updated each time a shipment or shipment/invoice is posted in Purchase Order Processing. The average lead time is calculated using the following formula: [(Number of Receipts) * (Average Lead Time) + (Received Date - Ordered Date)] / (...

Using Popups to supply variables
Hi All I sue this macro to find and replace a number in column I Columns("I:I").Select Selection.Replace What:="5.95", Replacement:="2.95", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False I'd like to replace the values in the macro , requesting variable input via popup. Can someone help? Effectively , there would be popups to request the column , the search number and the replace number. If it could also cycle back to the beginning on completion ...

Saving an entire workbook as a PDF using VBSCript
Hello all! I'm using a VBScript file which I've added to Server 2003 scheduler to automatically open up Excel, load it with a workbook which is in turn populated with data, print it out, dynamically come up with a filename which is figured off of the current date and time which is then appended to the original filename of the workbook, save it in HTML format, then close itself. Works like a charm. I've decided I don't want to save the file in HTML format but would instead like to save it in PDF. The reason being is HTML files can modified by any with a text editor. ...

eConnect is it in use?
I have come across an orphaned GP 9 client that has eConnect installed. No one there knows why eConnect is installed and I cannot find any applications that may be using it. Is there anyway to determine if it is actually in use currently doing something? Rich, A couple of things: 1) check for any econnect windows services running using the Services admin tool 2) in SQL, check the table called econnect_out table in the GP database. If it is not running, there should not be any data in there. You may want to monitor it for a few hours to see if anything pops into there and is cleared...

What outgoing mail server is used Outlook for MSN email address?
Setting up a new Internet account on Outlook asks for a POP3 and SMTP server, I use MSN, which requires an HTTP outgoing server for Explorer. MSN support agreed I could use my MSN Hotmail account on Outlook, but did not know how, help anyone? Set it up as an HTTP account, not POP3, and the server fields will be filled in for you. -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Visual QuickStart Guide) Need Help with Common Tasks? http://www.outlook-tips.net/beginner/ Outlook Tips: http://www.outlook-tips.net/ Outlo...

Use detailed data in one worksheet to create summary data as chart source
I need to export data from MS Access to Excel in order to create a chart. I can get the data into an Excel worksheet. However, this data needs to be summarized on a second worksheet. That summarized data then would be the source for a chart. My general thinking is to create an Excel template. The template would have the code necessary so that when a user exports data from Access to Excel, the following happens: 1) The 'Detail' worksheet in an instance of the template is populated with the Access data. This would occur via coding in my Access application. I already have this wor...

SUM using GROUP BY
Following is the skeleton of a simple query I wrote,to find the sum of values by grouping multiple columns ( from different tables joins).Now,even if the SUM(C.column3) is NULL( verified this by running a separate value specific query), it shows up with values from the previous columns! Not sure what is the problem here...Please help. SELECT A.column1, B.column2, ISNULL(SUM(C.column3),0) <FROM A JOIN B AND JOIN C> GROUP BY A.column1, B.column2 hi, ashwini.hari@gmail.com wrote: > Following is the skeleton of a simple query I wrote,to find the sum of > values by grouping multipl...

Might you need to debug apps you maintain on Windows 95 using VS2005?
If you would, you might like to add a vote to this bug report on the remote debugger for VS2005: http://lab.msdn.microsoft.com/productfeedback/viewfeedback.aspx?feedbackid=91ddde7d-6fa5-4549-8083-8408051d4863 Thanks Dave >If you would, you might like to add a vote to this bug report on the >remote debugger for VS2005: > >http://lab.msdn.microsoft.com/productfeedback/viewfeedback.aspx?feedbackid=91ddde7d-6fa5-4549-8083-8408051d4863 Thanks to the couple of people who voted - it would indeed appear that MS's assumption that hardly anyone wants to be able to debug their softwar...

Which template should I use?
Hello, I'm trying to make a simple flow chart of some web site page and data flow that includes user input some key info that is passed to varioius paths through the site and special pages. I'm not a programmer and don't understand any of the shapes for progamming charts and I dont need that level of sophistication anyway... I need to be able to show flow and key descion point logical flow and some small page capures... But it is really more than just a web site page layout so that doen't fit the billl I don't see in Visio 2003 where it defines what each shap is comm...

How can I use open/close Grouped cells in a Protected Worksheet?
Can anyone help me please - When I include Grouped cells in a Worksheet by using "Data" / "Group and Outline", excell will not allow opening or closing of the Grouped cells when the Worksheet is protected. Is there an easy way I can open/close Grouped cells in a Protected Worksheet? -- Andy Bowshell ------------------------------------------------------------------------ Andy Bowshell's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30469 View this thread: http://www.excelforum.com/showthread.php?threadid=501330 If you already have the ou...

Using multiple SMTP email addresses
How can I determine which email address an email was sent to (short of manually looking at the headers of the email)? I have multiple SMTP addresses associated with my AD account, and I want to set up rules in Outlook based on which address an email was sent to, but all Outlook seems to see is my default smtp address. This behavior is "by design". When Exchange 200x receives a message via SMTP is resolves it to an object in AD and rewrites the To: address to that of the primary SMTP address of the object that the message is destined for. "Josh Rolfe" <some...

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 Template modification using automation
I am using automation (from vb6 project) to open an excel template (test.xlt). I want to modify some cell values and then resave the template. I keep encountering a File Readonly error. Could someone post some sample code that would allow me to programmatically modify an excel template and then save template, overwriting old template tai Terry Holland I think you may get various responses based on why the file was marked readonly. If it was on a server that the user (you) only had read access, too, I think you're out of luck. If it was marked Readonly (via windows explorer's ...

RMS using Mercury Payment Systems
How do I configure RMS to use Mercury Payment Systems payment processing? "Fred" wrote: > How do I configure RMS to use Mercury Payment Systems payment processing? > > Use this link http://www.mercurypay.com/resources/RMSInstallGuidetpi.pdf It's the install guide pretty simple Thanks for your help. This looks pretty straight forward. But the links to the plugins don't work. "JereHurn@zeroedin" wrote: > > > "Fred" wrote: > > > How do I configure RMS to use Mercury Payment Systems payment processing? > > &g...