Need help finding a function, please

[plo888]

I'm looking for a function that will calculate results from data filtering.

Within my spreadsheet, I have a list of companies (Column A) followed by their sector code (column B), the eps result (either beat, miss, or meet) (column C) and then a return amount (Column D).

Which excel function would you suggest I use If I want filter for:

information technology (IT) sector AND

beat their eps

THEN only return back the average of the returns of all those IT companies?

At first glance, I'm think sumproduct with average function, but not certain.

Thanks in advance

 

[cstimart]

Sounds like AVERAGEIFS might be your function.

 

[T. Valko]

I'm looking for a function that will calculate results from data filtering.

Within my spreadsheet, I have a list of companies (Column A) followed by their sector code (column B), the eps result (either beat, miss, or meet) (column C) and then a return amount (Column D).

Which excel function would you suggest I use If I want filter for:

information technology (IT) sector AND

beat their eps

THEN only return back the average of the returns of all those IT companies?

At first glance, I'm think sumproduct with average function, but not certain.

Thanks in advance

What version of Excel are you using?

One of these will do. Either an AVERAGE(IF array formula or, in Excel 2007 or later, an AVERAGEIFS formula.

 

[markmzz]

Another formula:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Average for all data</td><td style="text-align: right;;">590.00</td><td style=";">Average</td><td style="text-align: right;;">514.60</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;"></td><td style="font-weight: bold;color: #0070C0;;">Filtered Data</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="font-weight: bold;;">Company</td><td style="font-weight: bold;;">Sector Code</td><td style="font-weight: bold;;">Eps Result</td><td style="font-weight: bold;;">Amount</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">Comp34</td><td style=";">Sector14</td><td style=";">miss</td><td style="text-align: right;;">601</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style=";">Comp57</td><td style=";">Sector14</td><td style=";">miss</td><td style="text-align: right;;">760</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style=";">Comp72</td><td style=";">Sector14</td><td style=";">beat</td><td style="text-align: right;;">513</td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style=";">Comp48</td><td style=";">Sector14</td><td style=";">miss</td><td style="text-align: right;;">136</td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style=";">Comp35</td><td style=";">Sector14</td><td style=";">miss</td><td style="text-align: right;;">563</td></tr></tbody></table><p style="width:2.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Comp</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B1</th><td style="text-align:left">=AVERAGE(<font color="Blue">$D$5:$D$23</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D1</th><td style="text-align:left">=SUBTOTAL(<font color="Blue">1,$D$5:$D$23</font>)</td></tr></tbody></table></td></tr></table><br />

Markmzz

 

[plo888]

Unfortunately, I'm using Excel 2003.
How will it look if using =average(if ??

 

[plo888]

This attempt didnt work
=average(if(C2:C134="Information Technology", G2:G134="Beat", D85:D134>7/1/2011, O2:O134))

I think i need an average if + and ??

 

[T. Valko]

This attempt didnt work
=average(if(C2:C134="Information Technology", G2:G134="Beat", D85:D134>7/1/2011, O2:O134))

I think i need an average if + and ??

Using that formula as a template...

You're better off using cells to hold the criteria:

• A2 = Information Technology
• A3 = Beat
• A4 = 7/1/2011

Array entered**:

=AVERAGE(IF(C2:C134=A2,IF(G2:G134=A3,IF(D85:D134>A4,O2:O134))))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[plo888]

Thank you all for your prompt responses

Because I have Office 2003, I used:

=AVERAGE(IF('Q22011'!$C2:$C137="Information Technology",IF('Q22011'!$G2:$G137="Beat",IF('Q22011'!$D2:$D137>7/1/2011,'Q22011'!$O2:$O137))))

and its looking ok so far.
and did use the ctrl shift return

lmk if I'm wrong here

 

[T. Valko]

Thank you all for your prompt responses

Because I have Office 2003, I used:

=AVERAGE(IF('Q22011'!$C2:$C137="Information Technology",IF('Q22011'!$G2:$G137="Beat",IF('Q22011'!$D2:$D137>7/1/2011,'Q22011'!$O2:$O137))))

and its looking ok so far.
and did use the ctrl shift return

lmk if I'm wrong here

Yes, you're wrong!

You're not testing a date with this expression:

'Q22011'!$D2:$D137>7/1/2011

You're testing:

'Q22011'!$D2:$D137 > 7 divided by 1 divided by 2011

If you don't want to use a cell to hold the date then try this...

The unambiguous method:

'Q22011'!$D2:$D137>DATE(2011,7,1)

 

[plo888]

Biff, I will never doubt you.
Strangely, I tried it with the rest of the sectors and they match my manual labor filtering.

I will adjust accordingly and greatly appreciate your follow up.