Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
If the average
Hello,
In cell A1 I would like to put a month and year, for this example we can use December 08, but I would also like to be able to change it to January 08 and match the dates in the range of column D In Cell B1 I have a name, for this example we can use Jim In Cell C1 I will return the results of the formuls (you fill this in) Column D has a variety of dates from January 01 2001 to December 31 2008 Column E has various names including the match we are looking for "Jim" Column F has data generally looking like this: .41 Can you help me with a formula that will take all the the data entries (column F) that match Jim (column E) for December 08 and average them. If December 08 had 20 entries for Jim in column F then take the data add it all together and divide it by 20. I hope this is clearer than mud. Thank you very much you "Excel Yoda's" Jim |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
If the average
Assuming A1 down contains real dates,
then something like this In C1, array-entered, ie press CTRL+SHIFT+ENTER to confirm the formula: =AVERAGE(IF((TEXT(D1:D4,"mmmyy")=TEXT(A1,"mmmyy")) *(E1:E4=B1),F1:F4)) Adapt the ranges to suit your actuals, fix it with $ signs if you are copying C1 down -- Max Singapore http://savefile.com/projects/236895 Downloads:22,000 Files:370 Subscribers:66 xdemechanik --- "Jim" wrote: In cell A1 I would like to put a month and year, for this example we can use December 08, but I would also like to be able to change it to January 08 and match the dates in the range of column D In Cell B1 I have a name, for this example we can use Jim In Cell C1 I will return the results of the formuls (you fill this in) Column D has a variety of dates from January 01 2001 to December 31 2008 Column E has various names including the match we are looking for "Jim" Column F has data generally looking like this: .41 Can you help me with a formula that will take all the the data entries (column F) that match Jim (column E) for December 08 and average them. If December 08 had 20 entries for Jim in column F then take the data add it all together and divide it by 20. I hope this is clearer than mud. Thank you very much you "Excel Yoda's" Jim |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
If the average
Max,
This is perfect!!! Can you also give me input about how to write this formula so the values are added and not summed? Thank you very much! Jim "Max" wrote: Assuming A1 down contains real dates, then something like this In C1, array-entered, ie press CTRL+SHIFT+ENTER to confirm the formula: =AVERAGE(IF((TEXT(D1:D4,"mmmyy")=TEXT(A1,"mmmyy")) *(E1:E4=B1),F1:F4)) Adapt the ranges to suit your actuals, fix it with $ signs if you are copying C1 down -- Max Singapore http://savefile.com/projects/236895 Downloads:22,000 Files:370 Subscribers:66 xdemechanik --- "Jim" wrote: In cell A1 I would like to put a month and year, for this example we can use December 08, but I would also like to be able to change it to January 08 and match the dates in the range of column D In Cell B1 I have a name, for this example we can use Jim In Cell C1 I will return the results of the formuls (you fill this in) Column D has a variety of dates from January 01 2001 to December 31 2008 Column E has various names including the match we are looking for "Jim" Column F has data generally looking like this: .41 Can you help me with a formula that will take all the the data entries (column F) that match Jim (column E) for December 08 and average them. If December 08 had 20 entries for Jim in column F then take the data add it all together and divide it by 20. I hope this is clearer than mud. Thank you very much you "Excel Yoda's" Jim |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
If the average
"Jim" wrote:
Max, This is perfect!!! Welcome, good to hear. Please go back and mark that response by pressing the YES button there (like the one below) .. how to write this formula so the values are added and not summed? But adding is the same as summing? If you want to SUM instead of AVERAGE for the previous multi-criteria scenario ... in another cell, paste the same expression, then just edit/replace AVERAGE with SUM in the formula bar, then array-enter the expression as before. That should do it for you. And then press the YES button below in this response. P/s: Similarly, if you want to COUNT for the multi-criteria scenario, replace AVERAGE with COUNT -- Max Singapore http://savefile.com/projects/236895 Downloads:22,000 Files:370 Subscribers:66 xdemechanik --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
A formula to AVERAGE IF but only average a set number of values | Excel Worksheet Functions | |||
Find monthly average but have average automatically configured | Excel Discussion (Misc queries) | |||
Error Handling #N/A with AVERAGE Function - Average of values in Row | Excel Worksheet Functions | |||
Weighed Average of a weiged average when there are blanks | Excel Discussion (Misc queries) | |||
how does one convert text to a formula "average(A:A)" to =average( | Excel Worksheet Functions |