RMSE in EXCEL help

[SMUboi]

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

 

[Bernard Liengme]

You should try to rephrase the question. Give us an example of the data to
be used.

 

[Jon Peltier]

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/
_______