Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
average of non-contiguous range based on criteria
I have a summary page with data in rows. Within that row I want the average of all the numbers that have a specific value in the cell before it. For instance: 3 3500 3 3500 7 4000 7 4000 28 5000 28 I want the average of all the numbers with 7 before it or 4000. I have 10 sets of data to shearch from. Do I need a vba program to do this? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
average of non-contiguous range based on criteria
Maybe something like this
=SUMPRODUCT(--(A1:K1=7),B1:L1)/COUNTIF(A1:L1,7) will work for you example -- Regards, Peo Sjoblom "gtslabs" wrote in message ... I have a summary page with data in rows. Within that row I want the average of all the numbers that have a specific value in the cell before it. For instance: 3 3500 3 3500 7 4000 7 4000 28 5000 28 I want the average of all the numbers with 7 before it or 4000. I have 10 sets of data to shearch from. Do I need a vba program to do this? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
average of non-contiguous range based on criteria
Worked Great! - Thank you,
Could you please expalin the use of the -- ? I have never seen this before. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
average of non-contiguous range based on criteria
Please remember to quote enough of the message to which you are replying to
make the context of your question clear. I you are talking about the use of the double unary minus, such as in a SUMPRODUCT formula, try: http://www.mcgimpsey.com/excel/formulae/doubleneg.html or http://xldynamic.com/source/xld.SUMPRODUCT.html#format -- David Biddulph "gtslabs" wrote in message ... Worked Great! - Thank you, Could you please expalin the use of the -- ? I have never seen this before. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
average based on criteria | Excel Worksheet Functions | |||
need to average based on criteria | Excel Discussion (Misc queries) | |||
DSUM - non contiguous criteria range | Excel Worksheet Functions | |||
Calculating an average based on 2 and 3 criteria | Excel Worksheet Functions | |||
caluculate an average based on 2 criteria | Excel Discussion (Misc queries) |