Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Average Valid Values
Hi All:
Is there anyone who can tell me how to calculate average of valid values? I have a huge data set with 1 to 6 valid values and 0 as missing. I need to calculate average of only valid values (1 to 6) for each record but to exclude 0 (missing). For exmple, 1, 3, 3, 0, 5 The average for this record should be 3 because 0 is missing value and should not be included. But when I use Excel built in function Average, the result is 2.4. Any help will be very very appreciated. Charles |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Average Valid Values
How about:
=SUM(A1:A5)/COUNTIF(A1:A5,"<"&0) Adjust the range to match your thousands of values. Charles Deng wrote: Hi All: Is there anyone who can tell me how to calculate average of valid values? I have a huge data set with 1 to 6 valid values and 0 as missing. I need to calculate average of only valid values (1 to 6) for each record but to exclude 0 (missing). For exmple, 1, 3, 3, 0, 5 The average for this record should be 3 because 0 is missing value and should not be included. But when I use Excel built in function Average, the result is 2.4. Any help will be very very appreciated. Charles -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data Valid - refers to the values of 2 cells | New Users to Excel | |||
Formula to check against a list of valid values | Excel Worksheet Functions | |||
How to valid the values? | Excel Worksheet Functions | |||
Pop-up menu with valid values | Excel Programming | |||
Valid ControlSource values | Excel Programming |