| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
=AVERAGE(b7:b20) is counting fiels that are zero
I brought cell information from worksheet B into worksheet A, by typing = in
a cell and then clicking on cell in worksheet B and hitting enter. Data in that cell is empty. So excell placed a "0" in the final cell. When I average the column it of course adds the zero's. I don't want it to do that, only the numbers gtreater that '0". How do I get this accomplished? |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
=AVERAGE(b7:b20) is counting fiels that are zero
Instead of something like:
=Sheet1!A1 use: =if(Sheet1!A1="","",Sheet1!A1) -- Gary''s Student - gsnu200908 "outrigger" wrote: I brought cell information from worksheet B into worksheet A, by typing = in a cell and then clicking on cell in worksheet B and hitting enter. Data in that cell is empty. So excell placed a "0" in the final cell. When I average the column it of course adds the zero's. I don't want it to do that, only the numbers gtreater that '0". How do I get this accomplished? |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
=AVERAGE(b7:b20) is counting fiels that are zero
either modify the orginal formulas to
=IF(Sheet1!A1="","",Sheet1!A1) OR average to be modified to as below =SUMIF(b7:b20,"0")/COUNTIF(b7:b20,"0") or 'array entered (ctrl+Shift+Enter) =AVERAGE(IF(b7:B200,B7:B20)) If this post helps click Yes --------------- Jacob Skaria "outrigger" wrote: I brought cell information from worksheet B into worksheet A, by typing = in a cell and then clicking on cell in worksheet B and hitting enter. Data in that cell is empty. So excell placed a "0" in the final cell. When I average the column it of course adds the zero's. I don't want it to do that, only the numbers gtreater that '0". How do I get this accomplished? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Counting Months to get an Average | Excel Worksheet Functions | |||
| Pivot Table Clear All Fiels | Excel Discussion (Misc queries) | |||
| Average of a row of numbers not counting 0's | Excel Worksheet Functions | |||
| show in a excel graphic a total percentage for 5 diff data fiels | Charts and Charting in Excel | |||
| compare mulitple fiels... then, order the files by header... then, save | Excel Programming | |||
Linear Mode
