Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 16
Default Average Formula to display blank cell if named range is blank

Hello

I have looked through the Excel posts but could not find an answerto the
following question. I would be grateful for any help.

I would like a formula that will display the average of columns A:C in
column D. This works fine if there are values in columns A:C, eg
5.0+5.0+7.0=5.6

If, however, the cells in A:C are blank, #DIV/0! is displayed.

A B C D
5.0 5.0 7.0 5.6
4.5 4.5 5.0 4.6
#DIV/0!

I would like column D to be blank if A:C are blank
& if values are displayed in A:C for the average value to be displayed in
column D.

I am using Excel 2000.

Many thanks.

Rachael
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 772
Default Average Formula to display blank cell if named range is blank

you can do an if(iserror(formula),"",formula
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Rachael F" wrote:

Hello

I have looked through the Excel posts but could not find an answerto the
following question. I would be grateful for any help.

I would like a formula that will display the average of columns A:C in
column D. This works fine if there are values in columns A:C, eg
5.0+5.0+7.0=5.6

If, however, the cells in A:C are blank, #DIV/0! is displayed.

A B C D
5.0 5.0 7.0 5.6
4.5 4.5 5.0 4.6
#DIV/0!

I would like column D to be blank if A:C are blank
& if values are displayed in A:C for the average value to be displayed in
column D.

I am using Excel 2000.

Many thanks.

Rachael

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 16
Default Average Formula to display blank cell if named range is blank

Thank you very much, John. An iserror formula did the trick.

Best wishes.

Rachael

"John Bundy" wrote:

you can do an if(iserror(formula),"",formula
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Rachael F" wrote:

Hello

I have looked through the Excel posts but could not find an answerto the
following question. I would be grateful for any help.

I would like a formula that will display the average of columns A:C in
column D. This works fine if there are values in columns A:C, eg
5.0+5.0+7.0=5.6

If, however, the cells in A:C are blank, #DIV/0! is displayed.

A B C D
5.0 5.0 7.0 5.6
4.5 4.5 5.0 4.6
#DIV/0!

I would like column D to be blank if A:C are blank
& if values are displayed in A:C for the average value to be displayed in
column D.

I am using Excel 2000.

Many thanks.

Rachael

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,651
Default Average Formula to display blank cell if named range is blank

=IF(COUNT(A2:C2)=0,"",AVERAGE(A2:C2))
--
David Biddulph

"Rachael F" wrote in message
...
Hello

I have looked through the Excel posts but could not find an answerto the
following question. I would be grateful for any help.

I would like a formula that will display the average of columns A:C in
column D. This works fine if there are values in columns A:C, eg
5.0+5.0+7.0=5.6

If, however, the cells in A:C are blank, #DIV/0! is displayed.

A B C D
5.0 5.0 7.0 5.6
4.5 4.5 5.0 4.6
#DIV/0!

I would like column D to be blank if A:C are blank
& if values are displayed in A:C for the average value to be displayed in
column D.

I am using Excel 2000.

Many thanks.

Rachael



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
AAArgh, can I display a blank cell if the formula value doesn't co Matt Excel Discussion (Misc queries) 4 March 17th 06 09:24 PM
how to get excel to display blank if reference cell blank silent1(not) Excel Worksheet Functions 1 December 2nd 05 03:49 PM
Can a formula display output to an otherwise blank cell? T.Morris-TX Excel Worksheet Functions 2 November 29th 05 01:32 PM
Dynamic Named Range with blank cells tjtjjtjt Excel Discussion (Misc queries) 3 October 5th 05 08:10 PM
Blank cells in named range- how to ignore them when making my graph? Help plz! KR Excel Discussion (Misc queries) 0 August 24th 05 02:35 PM


All times are GMT +1. The time now is 11:50 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"