Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average not including Zeros/Blanks
I am using the following formula for an average function, but it doesn't
quite do what I need it to do. =AVERAGE(IF(F3:F38<0,F3:F37)) There are cells in this that I need to omit from the average, because they are a subtotaled average for the week. I need to somehow remove F10, F18, F26, F34, and F38 while the formula still calculates the average for the cells that have information in them, omitting the blank cells. I did some looking around on the forum (that's where I got the above formula) and didn't find anything that would fit. Thanks. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average not including Zeros/Blanks
The ARRAY formula presented should work IF you array enter it using
CTRL+SHIFT+ENTER -- Don Guillett Microsoft MVP Excel SalesAid Software "DaS" wrote in message ... I am using the following formula for an average function, but it doesn't quite do what I need it to do. =AVERAGE(IF(F3:F38<0,F3:F37)) There are cells in this that I need to omit from the average, because they are a subtotaled average for the week. I need to somehow remove F10, F18, F26, F34, and F38 while the formula still calculates the average for the cells that have information in them, omitting the blank cells. I did some looking around on the forum (that's where I got the above formula) and didn't find anything that would fit. Thanks. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average not including Zeros/Blanks
You do NOT type in the brackets. You enter the formula using cse Show your layout or send me a file to the address below along with copies of these posts snipped onto a sheet and full details of what you desire. -- Don Guillett Microsoft MVP Excel SalesAid Software "DaS" wrote in message ... My apoligies...I forgot to type in the brackets. It is entered as an array, but the problem is that the "subtotaled" cells for the week, that average the week ahead, are being included into the overall picture. I need those cells removed from the equation. "Don Guillett" wrote: The ARRAY formula presented should work IF you array enter it using CTRL+SHIFT+ENTER -- Don Guillett Microsoft MVP Excel SalesAid Software "DaS" wrote in message ... I am using the following formula for an average function, but it doesn't quite do what I need it to do. =AVERAGE(IF(F3:F38<0,F3:F37)) There are cells in this that I need to omit from the average, because they are a subtotaled average for the week. I need to somehow remove F10, F18, F26, F34, and F38 while the formula still calculates the average for the cells that have information in them, omitting the blank cells. I did some looking around on the forum (that's where I got the above formula) and didn't find anything that would fit. Thanks. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average not including Zeros/Blanks
To start with, you probably need the 2 arrays to be the same length.
-- David Biddulph "DaS" wrote in message ... I am using the following formula for an average function, but it doesn't quite do what I need it to do. =AVERAGE(IF(F3:F38<0,F3:F37)) There are cells in this that I need to omit from the average, because they are a subtotaled average for the week. I need to somehow remove F10, F18, F26, F34, and F38 while the formula still calculates the average for the cells that have information in them, omitting the blank cells. I did some looking around on the forum (that's where I got the above formula) and didn't find anything that would fit. Thanks. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average not including Zeros/Blanks
Well, no, it won't.
First, the ranges need to be the same size. Second, it won't remove the cells the OP wants it to. This should work: =SUMPRODUCT(--(MOD(ROW(F3:F37),8)<2), --(F3:F37<0),F3:F37)/ SUMPRODUCT(--(MOD(ROW(F3:F37),8)<2), --(F3:F37<0)) In article , "Don Guillett" wrote: The ARRAY formula presented should work IF you array enter it using CTRL+SHIFT+ENTER -- Don Guillett Microsoft MVP Excel SalesAid Software "DaS" wrote in message ... I am using the following formula for an average function, but it doesn't quite do what I need it to do. =AVERAGE(IF(F3:F38<0,F3:F37)) There are cells in this that I need to omit from the average, because they are a subtotaled average for the week. I need to somehow remove F10, F18, F26, F34, and F38 while the formula still calculates the average for the cells that have information in them, omitting the blank cells. I did some looking around on the forum (that's where I got the above formula) and didn't find anything that would fit. Thanks. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average not including Zeros/Blanks
I think the OP meant that he didn't type in the brackets in his post. He
obviously knows what array-entering means... In article , "Don Guillett" wrote: You do NOT type in the brackets. You enter the formula using cse Show your layout or send me a file to the address below along with copies of these posts snipped onto a sheet and full details of what you desire. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average not including Zeros/Blanks
That worked great. Thanks JE, also for identifying the whole "bracket"
mix-up. I hate to be a bother, but could you explain how the formula works? "JE McGimpsey" wrote: Well, no, it won't. First, the ranges need to be the same size. Second, it won't remove the cells the OP wants it to. This should work: =SUMPRODUCT(--(MOD(ROW(F3:F37),8)<2), --(F3:F37<0),F3:F37)/ SUMPRODUCT(--(MOD(ROW(F3:F37),8)<2), --(F3:F37<0)) In article , "Don Guillett" wrote: The ARRAY formula presented should work IF you array enter it using CTRL+SHIFT+ENTER -- Don Guillett Microsoft MVP Excel SalesAid Software "DaS" wrote in message ... I am using the following formula for an average function, but it doesn't quite do what I need it to do. =AVERAGE(IF(F3:F38<0,F3:F37)) There are cells in this that I need to omit from the average, because they are a subtotaled average for the week. I need to somehow remove F10, F18, F26, F34, and F38 while the formula still calculates the average for the cells that have information in them, omitting the blank cells. I did some looking around on the forum (that's where I got the above formula) and didn't find anything that would fit. Thanks. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average not including Zeros/Blanks
Briefly:
(F3:F37<0) returns an array of 35 TRUE/FALSE values. --(F3:F37<0) converts the array to 1/0 (see http://www.mcgimpsey.com/excel/doubleneg.html for more explanation). --(MOD(ROW(F3:F37),8)=2) returns an array of 35 1s/0s depending on whether each row's row number, when divided by 8, has a remainder of 2. Those arrays are multiplied to return an array of 1s/0s (1 for rows for which the remainder<2 and the value<0). In the denominator, SUMPRODUCT() then adds those 1/0 to get a count of valid values. In the numerator, the arrays are further multiplied by the array of values in F3:F37. Where both the remainder < 2 and the value <0, the cell's value is returned in the array. SUMPRODUCT() then adds those values in the array. Dividing the sum of valid values by the count of valid values gives the average. In article , DaS wrote: That worked great. Thanks JE, also for identifying the whole "bracket" mix-up. I hate to be a bother, but could you explain how the formula works? "JE McGimpsey" wrote: Well, no, it won't. First, the ranges need to be the same size. Second, it won't remove the cells the OP wants it to. This should work: =SUMPRODUCT(--(MOD(ROW(F3:F37),8)<2), --(F3:F37<0),F3:F37)/ SUMPRODUCT(--(MOD(ROW(F3:F37),8)<2), --(F3:F37<0)) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
formulas blanks vs zeros | Excel Discussion (Misc queries) | |||
Fomat cells for IP address including leading Zeros | Excel Worksheet Functions | |||
lookup on Table including blanks - | Excel Worksheet Functions | |||
in excel, how do i get an average without including the zeros? | Excel Worksheet Functions | |||
How do I average a column without including zeros | Excel Discussion (Misc queries) |