Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2
Default If cell is left blank, or equals zero, then cell equals a different cell

I'm working on a relatively easy spreadsheet and just can't figure
this one out.

I'd like the average of the column (which I already have a cell for)
to represent any cell in which the user doesn't enter a number, or
enters zero.

Also, I'd like this cell to be differentiated (highlighted, different
color font, etc.) so as to know when this condition is used.

Many thanks,
John

  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11,058
Default If cell is left blank, or equals zero, then cell equals a differen

Say we have a column of values that may contain either blanks or zeros. We
want to average only the non-zero, non-blank values:

=SUM(A:A)/COUNTIF(A:A,"0")


--
Gary''s Student - gsnu200714


"John McMurry" wrote:

I'm working on a relatively easy spreadsheet and just can't figure
this one out.

I'd like the average of the column (which I already have a cell for)
to represent any cell in which the user doesn't enter a number, or
enters zero.

Also, I'd like this cell to be differentiated (highlighted, different
color font, etc.) so as to know when this condition is used.

Many thanks,
John


  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2
Default If cell is left blank, or equals zero, then cell equals a differen

On Apr 12, 10:08 am, Gary''s Student
wrote:
Say we have a column of values that may contain either blanks or zeros. We
want to average only the non-zero, non-blank values:

=SUM(A:A)/COUNTIF(A:A,"0")


Yes, however, that isn't what I'm asking. I apologize if I wasn't
clear.

I want to know the formula for a cell whe

data will be entered by the user and if the user leaves the cell
blank, or inputs zero, then the cell will equals another cell in the
worksheet

AND

the cell color or font will reflect the conditional situation.

Thanks,
John



  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11,058
Default If cell is left blank, or equals zero, then cell equals a diff

Now I understand. Let's say we have a table of values from A1 through A9.
We want the average, but if any of the values in the table is either blank or
zero, then use the contents of B1 instead for that value.


For example: if B1 contains 13 and the table contains:

1
2
3
4
5
6
7
8
9
then the average should be 5, but if the table is:

1
2
3
4
0
6
7
8
9
then the average would be 5.888888889
since we would use the 13 in place of the 0

The formula is:

=(SUM(A1:A9)+(9-COUNTIF(A1:A9,"0"))*B1)/9

This can easily be adjust for any table size or location and any substitute
cell.

To highlight the cells use Conditional Formatting and test for the cell
value equal 0 or blank.
--
Gary''s Student - gsnu200715
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
Very Basic Problem - Merged Cell Equals Contents of a Single Cell jollynicechap Excel Worksheet Functions 3 December 29th 06 09:16 PM
If cell value equals zero Steve Excel Worksheet Functions 2 March 3rd 06 03:57 AM
How to create/run "cell A equals Cell B put Cell C info in Cell D abmb161 Excel Discussion (Misc queries) 5 January 26th 06 07:36 PM
If cell equals, give total of another cell Lori Burton Excel Discussion (Misc queries) 2 January 12th 06 05:31 PM
If cell equals, give total of another cell Lori Burton Excel Discussion (Misc queries) 1 January 12th 06 04:48 PM


All times are GMT +1. The time now is 12:59 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"