Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
program formula to ignore any cell displaying "N/A"
I have built a complicated workbook that pulls from
individual worksheets. I am searching for a way to build a formula that will calculate a range of cells. That range consists of some that are used and some that are not. The ones that are not used are displaying "N/A" as a result of the formula build. Can anyone tell me how to build a formula and or function formula that can calculate the range using only the cells with a value in them. That range will remain constant but the cells will change from "N/A" to a value each time it is used. Thanks for your help Ed P |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
program formula to ignore any cell displaying "N/A"
Ed P,
My Test range is P2:P12 to sum =SUMIF(P2:P12,"<#N/A") to count =COUNTIF(P2:P12,"<#N/A") and array formulas (hit Enter while keeping Ctrl and Shift keys down) =AVERAGE(IF(ISERROR(P2:P12),"",P2:P12)) =MAX(IF(ISERROR(P2:P11),"",P2:P11)) HTH Cecil "Ed P" wrote in message ... I have built a complicated workbook that pulls from individual worksheets. I am searching for a way to build a formula that will calculate a range of cells. That range consists of some that are used and some that are not. The ones that are not used are displaying "N/A" as a result of the formula build. Can anyone tell me how to build a formula and or function formula that can calculate the range using only the cells with a value in them. That range will remain constant but the cells will change from "N/A" to a value each time it is used. Thanks for your help Ed P |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help displaying "Maximum" or "Minimum" if cell is max or min in a group? | Excel Discussion (Misc queries) | |||
How to ignore hidden columns in an "Average" formula? | Excel Discussion (Misc queries) | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
use "button" to make calculation ignore a cell | Excel Worksheet Functions | |||
Complex if test program possible? If "value" "value", paste "value" in another cell? | Excel Discussion (Misc queries) |