Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Shaun E on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Excel - Dropping '0' values out of averages 5
- Thread starter AGlazer
- Start date
-
2
- #2
DaleWatson123321
Programmer
Aaron,
I just created a simply example using the following formula and it works fine.
=DSUM(data,1,crit)/DCOUNTA(data,1,crit)
If you're not familiar with database functions, I can email you the file if you like.
Or, here's a quick set of steps...
1) Enter a field name at the top of your column of numbers (you can color it white if you don't want it to show).
2) On a separate sheet, enter the same field name in a cell. In the next cell below the field name, enter: >0
3) Assign a range name (e.g. "crit"
to the criteria. a) Highlight the two cells, b) Hold down <Ctrl> and hit <F3>, c) Type the name, d) Hit <Enter>.
4) Assign a range name to the field of numbers. The top row of this range needs to be the row containing the field name. The bottom row can extend beyond the last row containing number - i.e. you can leave room for numbers to be added later.
5) Enter the formula. You can copy it from below...
=DSUM(data,1,crit)/DCOUNTA(data,1,crit)
Hope this helps. Please advise as to how it fits.
P.S. Once you get used to using Excel's database functions, you'll discover a "whole new world" of "RAW POWER". For example, you can extract data - selectively -from a database/list to a separate sheet - to generate reports, based on the same "criteria concept". You only need to modify the criteria to extract the precise data you require.
Regards, ...Dale Watson dwatson@bsi.gov.mb.ca
I just created a simply example using the following formula and it works fine.
=DSUM(data,1,crit)/DCOUNTA(data,1,crit)
If you're not familiar with database functions, I can email you the file if you like.
Or, here's a quick set of steps...
1) Enter a field name at the top of your column of numbers (you can color it white if you don't want it to show).
2) On a separate sheet, enter the same field name in a cell. In the next cell below the field name, enter: >0
3) Assign a range name (e.g. "crit"
to the criteria. a) Highlight the two cells, b) Hold down <Ctrl> and hit <F3>, c) Type the name, d) Hit <Enter>.4) Assign a range name to the field of numbers. The top row of this range needs to be the row containing the field name. The bottom row can extend beyond the last row containing number - i.e. you can leave room for numbers to be added later.
5) Enter the formula. You can copy it from below...
=DSUM(data,1,crit)/DCOUNTA(data,1,crit)
Hope this helps. Please advise as to how it fits.
P.S. Once you get used to using Excel's database functions, you'll discover a "whole new world" of "RAW POWER". For example, you can extract data - selectively -from a database/list to a separate sheet - to generate reports, based on the same "criteria concept". You only need to modify the criteria to extract the precise data you require.
Regards, ...Dale Watson dwatson@bsi.gov.mb.ca
-
2
- #3
DaleWatson123321
Programmer
Aaron,
Mike's suggestion is a good alternative - for situations such as this where the "criteria" is not complex.
A limitation, though, is that you cannot have Mike's type of formula extend to empty rows - at least at is. You would need to change it to ">0" from "<>0", as follows...
=SUM(Your_Range)/COUNTIF(Your_Range,">0"
Mike's use of "<>0", however is the proper criteria to use for the database function - if there will potentially be negative numbers. The use of "<>0" will NOT adversely affect the use of the database function.
Hope this helps.
Regards, ...Dale Watson dwatson@bsi.gov.mb.ca
Mike's suggestion is a good alternative - for situations such as this where the "criteria" is not complex.
A limitation, though, is that you cannot have Mike's type of formula extend to empty rows - at least at is. You would need to change it to ">0" from "<>0", as follows...
=SUM(Your_Range)/COUNTIF(Your_Range,">0"
Mike's use of "<>0", however is the proper criteria to use for the database function - if there will potentially be negative numbers. The use of "<>0" will NOT adversely affect the use of the database function.
Hope this helps.
Regards, ...Dale Watson dwatson@bsi.gov.mb.ca
- Thread starter
- #5
-
1
- #6
- Status
Similar threads
- Locked
- Question