Prev: Counting strings of consecutive numbers in a column
Next: How to get slope
From: Jamal on 27 Jan 2010 02:35
Dears; I have the same problem with lenghty formulas as
AVERAGEIF($C$5:$C$155,"mar",P6:P156) and it gives me#DIV/0! error until the
cell value is zero.
I would appreciate if you kindly assist.
best regards
Jamal




"Dallas MBA" wrote:

> Does anyone know if Excel allows you to hide a formula when the result of the
> formula equals "0"? I have a huge spreadsheet that makes it difficult to
> briefly scan the report and locate number values greater than "0" because
> there are so many formulas that equal "0".
>
> Existing View: 0 0 0 4 0 0 0 0 1 0 0 0 7 0 0 0 0 0
>
> Preferred View: 4 1 7
>
> I don't want to see the zeros as in the "Existing View" above and want the
> spreadsheet to appear like "Preferred View". I want to keep the formulas in
> the cells that equal "0", therefore I don't want to delete them to make the
> cells look blank. Any feedback is appreciated!
>
From: "David Biddulph" groups [at] on 27 Jan 2010 04:49
You might try
=IF(ISERROR(AVERAGEIF($C$5:$C$155,"mar",P6:P156)),"",AVERAGEIF($C$5:$C$155,"mar",P6:P156))
but I can't test it as AVERAGEIF is an Excel 2007 function.
--
David Biddulph

"Jamal" <Jamal(a)discussions.microsoft.com> wrote in message
news:B262E7E1-2054-4952-9301-090F622E7555(a)microsoft.com...
> Dears; I have the same problem with lenghty formulas as
> AVERAGEIF($C$5:$C$155,"mar",P6:P156) and it gives me#DIV/0! error until
> the
> cell value is zero.
> I would appreciate if you kindly assist.
> best regards
> Jamal
>
>
>
>
> "Dallas MBA" wrote:
>
>> Does anyone know if Excel allows you to hide a formula when the result of
>> the
>> formula equals "0"? I have a huge spreadsheet that makes it difficult to
>> briefly scan the report and locate number values greater than "0" because
>> there are so many formulas that equal "0".
>>
>> Existing View: 0 0 0 4 0 0 0 0 1 0 0 0 7 0 0 0 0 0
>>
>> Preferred View: 4 1 7
>>
>> I don't want to see the zeros as in the "Existing View" above and want
>> the
>> spreadsheet to appear like "Preferred View". I want to keep the
>> formulas in
>> the cells that equal "0", therefore I don't want to delete them to make
>> the
>> cells look blank. Any feedback is appreciated!
>>


 | 
Pages: 1
Prev: Counting strings of consecutive numbers in a column
Next: How to get slope