MS Excel 2010 max/min/average/median/mode formula and exceedance frequency for dataset analysis

I have a column of data in a workbook on a sheet (sheet1) comprising C2:C10000 which I want to analyse for MAX/MIN/AVERAGE/MEDIAN/MODE etc in a summary table on a separate sheet (sheet2).  Applying the simple appropriate FUNCTION formula such as =MAX('Sheet1'!C2:C10000) to the data range appears to work to a fashion and produces a resulting number.  However, as some of the dataset column cells include  0, NaN, -999 inputs the current results are I believe largely misleading (apart from max). How can I modify the various function formula to ignore these and heading titles in row C1 and produce more accurate results.?

Analysing the same data for probability exceedance on the separate sheet (sheet2) I have applied the formula =COUNTIF(SHEET1!C2:C10000,”>”&D10) where D10 is a threshold limit value I wish to determine exceedance over.  As above I am not sure if this  formula correctly calculates the number of data inputs exceeding the threshold limit whilst ignoring all invalid cells? How might this formula be modified to specify a range limit for example 30-40? The current output produced is a number of occurences where exceedance occurs - how can this be presented as a % frequency of all the valid data?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:

Can you post your sample file as it will be easy to provide you a solution post that..

Also it will be helpful in your sample file what kind of results you are expecting to see..

PVR101Author Commented:
Saurabh, attached as requested.

Saurabh Singh TeotiaCommented:
You can use this formula for average...


Open in new window

CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Saurabh Singh TeotiaCommented:
Also enclosed your workbook where i tweaked the formulas highlighted as yellow...


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PVR101Author Commented:
thanks so suggested average formula now averages all valid data i.e. excludes any 0,-999,NaN inputs -yes?

Are the other formula correct eg mode/median?or as believe still include invalid inputs.

What about min formula and desired result per the cell row comments? the min value should always be greater than 0.  Am I correct to say the  % frequencies are just a matter of exceedance number divided by the valid data count or another FREQUENCY function required?

many thanks.
PVR101Author Commented:
One last query - can the formula be modified so new row data can be added and auto update or as current need specify last daat input cell i.e. 33860 etc hence previous 10000 input so able to add data. in this scenario as cells from 33860 are currently blank/empty will the formula exclude when computing average/median/mode etc
PVR101Author Commented:
examining the threshold limit calc for >35  - to check i applied a filter on the data for all values above 35 including 35 and get a count of 59  - your formula returns a count of 52 so curious as to reason on this variance? probably something obvious i have missed.

i take it the sumproduct formula includes the number 30 when specify >30 and 40 when specify <40 for the range calc?
Saurabh Singh TeotiaCommented:
Their you go your revised workbook..I made following changes...

1. Made the range dyanmic with the name of frm so now when you add a new data in will auto add them in the formula..Just make sure you don't have blank rows in the data.
2.Sumproduct formula now looks at >35 and less then <40 since you said threshold 30-40.
3. You are right in Median and Mode it will look at the complete data, You can't remove the outliers which you want to remove otherwise..

PVR101Author Commented:
thanks - i note the revised  >35 count still results in 52 so still different to the filtered count of 59? which is correct?

there is a significant difference in the range excedance calc with the formula - applying similar filter for number values 30-40 inclusive i get a count of 407 against the formula result of 48 so afraid struggling a little to see why difference and which is correct?
Saurabh Singh TeotiaCommented:
When you apply filter check in that filter you are inlcuding values which are equal to 35 or 30..However in the formula you are just saying greater then once you change the formula or filter you will see matching data...
PVR101Author Commented:
OK  - variance of 52 versus 59 is due to fact 59 includes values of 35.

however, I cant still rationalise the fliter count of 407 values for numbers 30-40 all inclusive against calc sum of 48 or even 55 when alter formula to include values of 30 & 40 .
PVR101Author Commented:
aah - just realized the range used in cell is 35-40 and not 30-40 as per input cell and text in original Q.  result now correctly matches result of 55!

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.