hi,
I am trying to determine the average of a column of data while excluding some rows in that column. i have the row numbers that i want to exclude. Is there any way to determine the average without having to physically delete the rows.
any help is greatly appreciated! thanks!
Hi and welcome to the board,
how do you determine the rows to exclude?
Quoting entire posts clutters the forum and makes threads hard to read !
If you are pleased with a member's answer then use the Star icon to rate it
Click here to see forum rules
Thank you,
the rows are determined through a function which searches through another table to MATCH the values in the column. Meaning, that if a certain value exists in the table it would give me the row number for it.
i hope this makes sense.. lol
Sense, maybe, but I don't get it. Could you post a small example with some details please?
Quoting entire posts clutters the forum and makes threads hard to read !
If you are pleased with a member's answer then use the Star icon to rate it
Click here to see forum rules
ok so let's say that i want to calculate the average of the following column A
A
2
3
4
5
3
5
But what i want is to exlude 3 and 5 (rows 2 and 4) from the average.
Is there any way that i can do that?
I'm not sure how your data are structured (specifically, where are these row numbers listed that identify items to exclude?), but it sounds like the AVERAGE function might not work for this. However, if you can identify the rows in some sort of binary fashion (e.g., "INCLUDE", "EXCLUDE"), you could use a SUMIF for the total and COUNTIF for the n, for example, like this:
=SUMIF(B:B,"INCLUDE",A:A)/COUNTIF(B:B,"INCLUDE")
where column B identifies each row as either INCLUDE or EXCLUDE and column A contains the numbers you want to average.
Would that work?
To identify rows, perhaps you could use a function in column B like:
=IF(COUNTIF(E:E,ROW()),"EXCLUDE","INCLUDE")
where Column E contains a list of all row numbers to exclude.
Last edited by clownfish; 01-06-2009 at 03:04 PM.
On second thought, the solution I provided above is kind of silly, since if you're going to identify rows to include/exclude, you might as well just have the IF function pull the values you want to average from Col A to Col B and then use AVERAGE:
so, in B2, type this (assuming your data are in Col A starting at A2):
=IF(COUNTIF(E:E,ROW()),A2,"")
and somewhere else, type this:
=AVERAGE(B:B)
Just a thought....
If you can have a column that flags the rows to be ignored...eg a formula
that returns Show/Hide, use and AutoFilter to hide the unwanted rows.
Then you can use this version of the SUBTOTAL function:
That function will ignore anything in hidden filtered rows.=SUBTOTAL(1,A2:A100)
...or...if you hide the rows manually...use this variation:
=SUBTOTAL(101,A2:A100)
It will ignore ANY hidden row.
Other options for SUBTOTAL parameters:
Note: Adding 100 to any of those FuncNums causes the SUBTOTAL function to ignore HIDDEN rows, not just hidden FILTERED rows.Func Num__Function 1_________AVERAGE 2_________COUNT 3_________COUNTA 4_________MAX 5_________MIN 6_________PRODUCT 7_________STDEV 8_________STDEVP 9_________SUM 10_________VAR 11_________VARP
Example: =SUBTOTAL(103,A1:A20) counts non-blank, non-hidden cells.
thank you for all the replies!
what i ended up doin is that i identified the rows that i did not need by using a Vlookup function then, replaced the value of the rows that i did not need with blanks that way i was able to use the average function.
again thank you for the input however!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks