If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
AVERAGE - Disregard zero and numbers over 60?
I need to compute averages of several rows of numbers. Every cell contains
a number. None are negative. I want the average to disregard '0' and any number over 60. Thanks in advance. |
#2
|
|||
|
|||
AVERAGE - Disregard zero and numbers over 60?
Use an array** formula like this:
=AVERAGE(IF((A2:A60)*(A2:A660),A2:A6)) **Array formulas must be confirmed using Ctrl+Shift+Enter, not just Enter -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Ted" wrote: I need to compute averages of several rows of numbers. Every cell contains a number. None are negative. I want the average to disregard '0' and any number over 60. Thanks in advance. . |
#3
|
|||
|
|||
AVERAGE - Disregard zero and numbers over 60?
"Ted" wrote:
I need to compute averages of several rows of numbers. Every cell contains a number. None are negative. I want the average to disregard '0' and any number over 60. I presume by '0', you really mean the number zero, not the text "0". Try this array formula[*]: =average(if((0A1:A100)*(A1:A100=60),A1:A100)) The "*" functions as AND. You cannot use the AND function itself in this context. [*] An array formula is committed by pressing ctrl+shift+Enter instead of just Enter. You should see curly braces around the entire formula in the Formula Bar, e.g. {=formula}. You cannot enter the curly braces yourself; Excel inserts them to denote an array formula. If you make a mistake, select the cell, press F2, edit as needed, and finally press ctrl+shift+Enter. |
#4
|
|||
|
|||
AVERAGE - Disregard zero and numbers over 60?
Perfect! Thank you both for your help!
"Joe User" joeu2004 wrote in message ... "Ted" wrote: I need to compute averages of several rows of numbers. Every cell contains a number. None are negative. I want the average to disregard '0' and any number over 60. I presume by '0', you really mean the number zero, not the text "0". Try this array formula[*]: =average(if((0A1:A100)*(A1:A100=60),A1:A100)) The "*" functions as AND. You cannot use the AND function itself in this context. [*] An array formula is committed by pressing ctrl+shift+Enter instead of just Enter. You should see curly braces around the entire formula in the Formula Bar, e.g. {=formula}. You cannot enter the curly braces yourself; Excel inserts them to denote an array formula. If you make a mistake, select the cell, press F2, edit as needed, and finally press ctrl+shift+Enter. |
Thread Tools | |
Display Modes | |
|
|