Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Averaging two ranges on the same column (excluding zeros)
Hello - I have a range of numbers (formatted as %) that I am trying to
average. All the values are on one row. However, the numbers are broken into two ranges, since took two separate averages of the sub-totals using Excel 2007's "AverageIF" function. My problem is that I want to average the two ranges that appear on the same column, but since one of the cells contains the sub-total, I cannot use a contigous range. I need to average two ranges, excluding zero values. Essentially, something like this: =AVERAGEIF(A1:A24,"<0") AND =AVERAGEIF(A26:A30,"<0") Please assist. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Averaging two ranges on the same column (excluding zeros)
Essentially, something like this:
=AVERAGEIF(A1:A24,"<0") AND =AVERAGEIF(A26:A30,"<0") So, what's in A25? Your subtotal? Try this: =(SUM(A1:A30)-A25)/(COUNTIF(A1:A24,"<0")+COUNTIF(A26:A30,"<0")) -- Biff Microsoft Excel MVP "NoviceUser" wrote in message ... Hello - I have a range of numbers (formatted as %) that I am trying to average. All the values are on one row. However, the numbers are broken into two ranges, since took two separate averages of the sub-totals using Excel 2007's "AverageIF" function. My problem is that I want to average the two ranges that appear on the same column, but since one of the cells contains the sub-total, I cannot use a contigous range. I need to average two ranges, excluding zero values. Essentially, something like this: =AVERAGEIF(A1:A24,"<0") AND =AVERAGEIF(A26:A30,"<0") Please assist. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Averaging two ranges on the same column (excluding zeros)
"data" is a defined name range A1:A30 no quotes
=AVERAGE(IF((ROW(data)<ROW(A25))*(data<0),data)) ctrl+shift+enter, not just enter "NoviceUser" wrote: Hello - I have a range of numbers (formatted as %) that I am trying to average. All the values are on one row. However, the numbers are broken into two ranges, since took two separate averages of the sub-totals using Excel 2007's "AverageIF" function. My problem is that I want to average the two ranges that appear on the same column, but since one of the cells contains the sub-total, I cannot use a contigous range. I need to average two ranges, excluding zero values. Essentially, something like this: =AVERAGEIF(A1:A24,"<0") AND =AVERAGEIF(A26:A30,"<0") Please assist. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Averaging, but excluding zeros | Excel Worksheet Functions | |||
Averaging numbers in a column while excluding blank spaces and zer | Excel Worksheet Functions | |||
averaging separate cells in excel excluding 0 values | Excel Discussion (Misc queries) | |||
Averaging excluding blanks--Multiple Tabs | Excel Worksheet Functions | |||
Averaging excluding min and max numbers | Excel Worksheet Functions |