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?