Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
Muk Muk is offline
external usenet poster
 
Posts: 10
Default 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
  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up 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=thresholdA1""
    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.
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5,939
Default 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

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,722
Default 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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Adding random noise to data Augabog Excel Worksheet Functions 3 April 3rd 23 12:10 PM
Speech recog'n msg in Excel displays "No Signal". EarlyBirdie Excel Discussion (Misc queries) 2 March 26th 08 02:13 PM
less noise when working with CSV files Rick Lowe Excel Discussion (Misc queries) 0 June 5th 06 06:33 PM
Can I set up a signal in Excel to let me know when my due date is. pandie Excel Worksheet Functions 1 February 23rd 06 04:36 AM
how do i set up a template with noise Assessments cross ref R/A davd Excel Worksheet Functions 0 November 16th 05 05:24 PM


All times are GMT +1. The time now is 09:55 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"