RMSE in EXCEL help

Discussion in 'Microsoft Excel Worksheet Functions' started by SMUboi, Jul 8, 2004.

  1. SMUboi

    SMUboi Guest

    Hey guys,

    I am new to the forums here, I just started doing excel as a researc
    assistant at my school. I cannot figure out how to do this RMSE proble
    i have. I dont know it I need an add-in or what. Anyway i will try m
    best to explain, here goes.

    RMSE= sqrt((1/N)*sum((x-x')/x)^2)

    My RMSE's have to do with food tempurature, so i need an RMSE for ever
    experimental tempurature difference i have. So i need a RMSE for 1-40
    0-30, 3-29 and everything inbetween. 40*39/2 = 800 cells.

    My x and x' come from 2 different columns on two different sheets.
    My N in the is the difference between the two temperature values t
    find RMSE for. Also x and x' change with the chosen temp values, say m
    values are 4 degrees and 20 degrees then x and x' start with the value
    in the 4th row and end with the values in the 20th row for summation

    What i have been asked to do is create a function for one cell that
    can drag to a 2d array of cells in excel to create ALL these RMS
    values. So my columns would be labeled 0-40 and rows labeled 0-39. Thi
    way you can simply look at the table, line up the row and colum
    depending on the temperatures you need, and see the proper RMSE value


    So is there any good programs out there from doing RMSE or can someon
    help me do this, or can it be done?

    I have tried my best to explain, and will answer any questions.

    If you guys could help it would be greatly appriciated

    thanks in advanc
     
    SMUboi, Jul 8, 2004
    #1
    1. Advertisements

  2. You should try to rephrase the question. Give us an example of the data to
    be used.
     
    Bernard Liengme, Jul 8, 2004
    #2
    1. Advertisements

  3. SMUboi

    Jon Peltier Guest

    You need to check your formula, and be sure that you understand what
    each of the terms represent. You should have one average x' for a set of
    N values of x.

    The average or mean x' is of course given by

    x' = (1/N)*sum(x)

    where

    N = sample size
    x = particular measurement

    The standard deviation, s, is a measure of the average deviation of a
    set of x values from the mean x'. It is given by

    s = sqrt((1/(N-1))*sum((x-x')^2)

    This is an unbiased estimate of the average deviation of x from x'.

    RMSE - Root Mean Square Error - a measure of variation of a population
    from its mean:

    RMSE = sqrt((1/N)*sum((x-x')^2)

    RMSE is a biased predictor of standard deviation, meaning that it
    understates the error, when determined on a sample from a larger
    population. It is biased by sqrt((N-1)/N).

    You can calculate these statistical values in Excel. The following
    assume your values are in the range of cells rng.

    mean or average
    AVERAGE(rng)

    unbiased standard deviation
    STDEV(rng)

    biased standard deviation
    STDEVP(rng)

    - Jon
    -------
    Jon Peltier, Microsoft Excel MVP
    Peltier Technical Services
    Tutorials and Custom Solutions
    http://PeltierTech.com/
    _______
     
    Jon Peltier, Jul 11, 2004
    #3
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.
Similar Threads
There are no similar threads yet.
Loading...