Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Very Basic Problem - Merged Cell Equals Contents of a Single Cell | Excel Worksheet Functions | |||
If cell value equals zero | Excel Worksheet Functions | |||
How to create/run "cell A equals Cell B put Cell C info in Cell D | Excel Discussion (Misc queries) | |||
If cell equals, give total of another cell | Excel Discussion (Misc queries) | |||
If cell equals, give total of another cell | Excel Discussion (Misc queries) |