How to measure signal to noise (S/N) of a set of data in Excel?
 

Hi,

I am dealing with sets of real data and as would be expected there are lots
of 'noise'. I would like to set a baseline so that calculations are
performed only on the data above the baseline. I know that the kurtosis
function shows how "peaked" the data is but I am looking for a data sieve
that ignores the random noise and accepts the real data (say anything five
times higher than random noise).

Thanks

Answer: How to measure signal to noise (S/N) of a set of data in Excel?
 

Hi there!

To measure the signal to noise (S/N) of a set of data in Excel, you can follow these steps:

1. Calculate the average (mean) and standard deviation of your data set. You can do this by using the AVERAGE and STDEV functions, respectively. For example, if your data is in cells A1:A10, you can use the following formulas:
   
   - Average:
   Formula:

   ---

   =AVERAGE(A1:A10) 


   ---

   - Standard deviation:
   Formula:

   ---

   =STDEV(A1:A10) 


   ---

2. Determine the threshold for your baseline. You mentioned that you want to set a baseline that ignores random noise and accepts real data that is five times higher than the noise. One way to do this is to multiply the standard deviation by 5 and add it to the average. For example, if the average is 10 and the standard deviation is 2, the threshold would be 10 + (2 * 5) = 20.
   
3. Create a new column next to your data set and label it "Filtered Data" (or something similar). In the first cell of this column (e.g. B1), enter the following formula:
   
   
   Formula:

   ---

   =IF(A1=threshold, A1, "") 


   ---

   This formula checks if the value in cell A1 is greater than or equal to the threshold you calculated in step 2. If it is, it returns the value in cell A1. If it isn't, it returns an empty string (i.e. nothing is displayed in the cell).
   
4. Copy the formula in cell B1 and paste it into the rest of the cells in the "Filtered Data" column. This will apply the same filtering criteria to the entire data set.
   
5. You can now perform calculations on the filtered data set in column B, knowing that it only includes values that are above the baseline threshold you set.

How to measure signal to noise (S/N) of a set of data in Excel?
 

My preference for this is to use Standard Deviation. At +/- 2 standard
deviations you will be excluding the 5% of the data that most significantly
varies from the mean. That removes the big anomolies but leaves the majority
of the data in pool.
--
HTH...

Jim Thomlinson


"Muk" wrote:

Hi,

I am dealing with sets of real data and as would be expected there are lots
of 'noise'. I would like to set a baseline so that calculations are
performed only on the data above the baseline. I know that the kurtosis
function shows how "peaked" the data is but I am looking for a data sieve
that ignores the random noise and accepts the real data (say anything five
times higher than random noise).

Thanks

How to measure signal to noise (S/N) of a set of data in Excel?
 

A common way is to use an array* function with an IF statement, such as:

=SUM(IF(A2:A1005,A2:A100))

This would then only SUM the numbers in A2:A100 that are 5.

*Array functions are confirmed using Ctrl+Shift+Enter, not just Enter.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Muk" wrote:

Hi,

I am dealing with sets of real data and as would be expected there are lots
of 'noise'. I would like to set a baseline so that calculations are
performed only on the data above the baseline. I know that the kurtosis
function shows how "peaked" the data is but I am looking for a data sieve
that ignores the random noise and accepts the real data (say anything five
times higher than random noise).

Thanks