Free Microsoft Excel 2013 Quick Reference

Root Mean Square

Does Excel have a function for calculating the RMS of a list of values (as for a tolerance analysis) without having to do it manually?

ie: SQRT(a^2+b^2+c^2+...+n^2)

Post your answer or comment

comments powered by Disqus

does anyone know what the formula for calculating root mean square (RMS)
error is? Can this be done in Excel?


How do you get Root Mean Square Error out of a column?

I am starting on excel and woulkd like to know ho w to go about this.
1. I need to calculate the 3 point moving average and

2.Plot the data and moving averages on the same chart.

3. Use a multiplicative model to isoltate the seasonal variations and plot these on a separate graph.

4. Use average seasobal variations , correcting for bias if neccessary to forecast the output in the relevant periods for 2004

5.Obtain in addition the root mean square error over the 2003-2005 and use it to estimate the quality of forecasts in previous question.

The figures of how many recruits for the army for a particular department for entry points in the months below.

I am quite new to excel and need help in how to go about this question in excel i know it might sound dumb but i am new and on my way to learning from the seasoned excelists .....

Year 2003 January 300
May 173
September 206
December 103

2004 January 174
May 100
September 232
December 95

2005 January 316
May 93
September 346
December 213

2006 January 129
May 131
September 210
December 56

Considering a regression, I don't need the RMS of the trend line but the RMS
of the line of equality


I have written a user defined function to calulate the overall root mean square (rms) value of a spectrum which is shown below. The function works correctly however there seems to be a problem with it updating when switching between sheets in a workbook. For example if the function is used on two or more different sheets, when the workbook is loaded the rms values are displayed correctly. When i click on another sheet the functions don't seem to be updating and contain the same values that were on the first sheet. The function is contained within an addin (.xla)

Here's the code.

Function RMS(range)

Dim rw As Integer, cl As Integer, r As Integer, c As Integer, rOff As Integer
Dim r1 As Integer, c1 As Integer, r2 As Integer, c2 As Integer
Dim xarray(5000)  As Double, yarray(5000) As Double
Dim j As Integer
Dim x_num As Double, y_num As Double
Dim n As Integer
Dim Band As Double
Dim slope As Double
Dim freq1 As Double
Dim freqref As Double
Dim freq2 As Double
Dim value1 As Double
Dim y1 As Double
Dim yref As Double
Dim Count As Integer
Dim power As Double
Dim rms_val As Double
With range
    rw = .Rows.Count
    cl = .Columns.Count
    s = .Address(ReferenceStyle:=xlR1C1)
End With
r = InStr(1, s, "R")
r2 = InStr(r + 1, s, "R")
c = InStr(1, s, "C")
c2 = InStr(c + 1, s, "C")
r1 = Val(Mid(s, r + 1, c - (r + 1)))
c1 = Val(Mid(s, c + 1, r2 - (c + 2)))
If Val(ActiveSheet.Cells(r1, c1)) <> ActiveSheet.Cells(r1, c1) Then r1 = r1 + 1: rw = rw - 1
For i = 1 To rw
    xarray(i) = Val(ActiveSheet.Cells(i + r1 - 1, c1))
    yarray(i) = Val(ActiveSheet.Cells(i + r1 - 1, c1 + 1))

For j = 2 To rw
    x_num = xarray(j)
    y_num = yarray(j)
    If yarray(j) = yarray(j - 1) Then
        value1 = (xarray(j) - xarray(j - 1)) * yarray(j) '  {simple square}
        If xarray(j) = xarray(j - 1) Then
            value1 = 0 ' {vertical slope therefore no area}
            n = Int(xarray(j) - xarray(j - 1)) ' {Calculates no of bands} int instead of trunc
            If n = 0 Then n = 1 'added by Ed francis 21/02/05. If f2-f1 < 1 then use 1 band
            Band = (xarray(j) - xarray(j - 1)) / n '  {band width}
            slope = (Application.WorksheetFunction.Ln(yarray(j - 1) / yarray(j))) /
(Application.WorksheetFunction.Ln(xarray(j - 1) / xarray(j)))
            freq1 = xarray(j - 1)
            freqref = xarray(j - 1)
            freq2 = xarray(j - 1) + Band
            value1 = 0
            y1 = yarray(j - 1)
            yref = yarray(j - 1)
            For Count = 1 To n
                power = Exp(slope * Application.WorksheetFunction.Ln(freq2 / freqref))
                y2 = yref * power
                ymean = (y1 + y2) / 2
                value1 = value1 + ymean
                freq1 = freq2
                freq2 = freq1 + Band
                y1 = y2
            Next Count
            value1 = value1 * Band
        End If
    End If
    rms_val = rms_val + value1
Next j
RMS = (rms_val) ^ 0.5
End Function
Can anyone shed some light on the matter.

Many thanks


Hi, I would be grateful if anyone could give me some ideas how to get round this problem I have.

I have a large amount (~400) of data series, each consisting of about 900 X and Y values. For each set of X and Y values, I want to compare their plot to the equation Y=A*(X^(2/3)) , where A is a constant for that particular pair of series, and then choose the A value which has the smallest RMS (root mean square) error to the plot of X and Y.

This is fine for one pair of X and Y values - I simply plot a table of of new Y values based on different A values (from 0 to -1) and choose the one with the lowest RMS error. However, just for one data series this takes at least 10 minutes - how can I at least partly automate this process? I have hardly any programming knowledge but am farily competant with forumlae in excel. I have been told goal seek might be the solution to my problem?

Thanks in advance

I have fwg tabel
Mol% Counts
0.00225 836.95
0.00225 834.69
0.00225 835.18
0.00967 3515.53
0.00967 3513.75
0.00967 3516.45
0.01883 6837.86
0.01883 6834.00
0.01883 6829.18
0.04595 16645.62
0.04595 16658.36
0.04595 16634.59
0.05791 20938.43
0.05791 20939.91
0.05791 20919.43
0.07558 27318.70
0.07558 27337.69
0.07558 27348.80
0.09317 33587.92
0.09317 33598.91
0.09317 33586.73

I enter the linest function as follow to get the third order line
Excel returns:
3.20132E-17 -1.06333E-12 2.77498E-06 -7.54106E-05 #N/A
1.25454E-17 6.72512E-13 9.69992E-09 3.00599E-05 #N/A
0.999998275 4.6705E-05 #N/A #N/A #N/A
3284344.441 17 #N/A #N/A #N/A
0.021492985 3.70831E-08 #N/A #N/A #N/A

The first row gives the coefficients and the intercept.
The fifth row first column gives the s2SSR (sum of squares regression). So
far I understand OK.

What I am looking for is the sMSE. This figure should be 2.18136*10^-9
(according to an example analytical method).
Where can I find this in the above table that Exel returned? Do I need to do
some more calculations? Or enter another function altogether?

The analytical method I refer to is ISO 6974 part 2 ( analyses of natural gas)

Hi All,
I am trying to find the standard deviation of some "Journey Timings data" I have. Lets say in cells A2:A3000 I have specific input journey times in the format of 'H:MM:SS' starting at 8 mins i.e. they are incrementally '0:08:00 ; 0:08:01 ; 0:08:02 ; 0:08:03 .................................. 0:59:58 ; 0:59:59 ; 1:00:00'

Along the top row I have 'Jan ; Feb ; Mar ; Apr etc'

Finally, in all the cells of this table I have an array formula that counts the number of journey times that are = to column A and to row 1. So, for example, the number of March Journey Times = 0:11:22 may equal "9", however, 0:11:23 may = "0"

The problem is getting statistics from this data. The mean is OK (I have used the SUMPRODUCT function to say get an average Journey Time of '0:11:58'). However, I am struggling with the SD. I can't use the STDEV function as that will not return it in the format of H:MM:SS. Also, if I try to manually do it, it appears to come out wrong - I guess because when you try and square it (ie SQRT of mean squared difference from the mean) you are in fact squaring a number less than 1 (ie H:MM:SS format will be less than 1 in number format) and therefore the square is even smaller!

Not too sure if this makes sense but does anyone have any ideas?

Thanks in advance


Dear ms excel user/programmers:

I hope this question is not repeated before. But anyway

highly appreciate anyone can tell me how (or hint) to use ms excell
and/or visual c/c++, basic, or octave(emulate matlab) to solve:
(12-x)^2 + y^2 =15^2,

(by programming or using its built in function)?

that ^2 mean square.

looking to any excel user, mathmatician, and/or programmer's help,

Dear ms excel user/programmers:

I hope this question is not repeated before. But anyway

highly appreciate anyone can tell me how (or hint) to use ms excell
and/or visual c/c++, basic, or octave(emulate matlab) to solve:
(12-x)^2 + y^2 =15^2,

(by programming or using its built in function)?

that ^2 mean square.

looking to any excel user, mathmatician, and/or programmer's help,

how do i take the square root of -25...(with the engineering functions...?)

thank you.

Square Root Spiral Function Graph...

i was looking for vba code to graph the Square Root Spiral from Pythagorus in Excel...?


thank you.


i am trying to use the square root formula to work out the top length of the picture on the userform from the numbers i have in the textbox's.

It is basically a 10x5 rectangle and i need to find the diagonal length

which should be 11.18 but it is returning 10

can anyone see what iam doin wrong

Hi All

I am trying to find the square root of a number and then return it as a rounded number. The code below displays the square root of the InputBox number, example:

If I enter 24 in the InputBox, 4.898979 is displayed in the MsgBox. I would like to round it so that 5 is displayed. Any help with the code would be great.

Sub SQRoot()
    num = InputBox("Enter a number", "Square Root Calculator", "0")
    If num < 0 Then
        MsgBox "You can't take the square root of a negative number like " & _
        num, vbOKOnly + vbExclamation
    ElseIf Len(num) = 0 Then
        Exit Do
        MsgBox "The square root of " & num & " is " & Sqr(num), vbOKOnly
    End If
End Sub

How do I perform square root function in excel?

I have a spreadsheet, column A1 is X, Column A2 is Y and under X in column A2,A3 are numbers 2,4,500. I want the numbers in column A to have the square root calculation in column B2,B3.......

Below is the macro I have been working on and can't seem to get it, I think I am missing some components, not sure, not an expert, any help appreciated:

Sub Squareroot()
' Squareroot Macro
' Macro recorded 8/20/2010 by W, Ron

Sub sq()
Dim n As Integer
Dim j As Integer
Dim Z As Variant
'Count size of array
n = Range("x").Count
'Do loop
For j = 1 To n
Z = Range("x").Cells(j).Value
Range("y").Cells(j).Value = Z * Z
End Sub

How can roots other than square be calculated in excel (i.e. cube roots, etc)?


I need to write a formula which works out the square root to the power of a cell, ie if the square root of 100 = 10 i need the square root of 100 to the power of 3 (for example) which = 4.642.

Any help would be greatly received.


what steps do i take to find the square root of a number

How do you calculate the square root to the fifth or

square root to the fourth power, etc. in MS Excel?

Thank you.


I have this formula:


Now, that = 0.33

I need to take the square root of that 0.33 to get 5.75.

Where do I put the ^1/2?


Hi, I need a formula to tell me whether a number is a square number, or I can do it so to see if the square root of a number is an interger.

So far Iv been doing

A1 = √2
A2 = AND(RIGHT(A1,1)=INT(A1))

True or false outcome is fine, and it has been working fine on some examples, but the problem comes when I have √49, as it is 2 digits long. I've seen a formula similar to find the root symbol, and look at all the numbers upto this point (maybe FIND), could some one help me to do this?

Either that or another formula would be good!


Hello! I need a little help. I want to make a code that would use the Babylonian method of finding the square root

This one's a short explanation from wikipedia:


And here's my scratch code: I can't seem to make it work.
Sub Sqrt()

Dim S As Integer
Dim N As Integer
Dim ctr As Integer

S = InputBox("Enter Number")

Do Until ctr = S ^ (1 / 2)
N = S / 2
If ctr = (1 / 2) * (N + S / N) Then
MsgBox (ctr)
N = ctr
End If

End Sub
What do I need to change? I also want to show the answer with a Message Box. Thank you!

I have data recorded from an experiment which is of the form y proportional to the square root of x. I would like to plot a line of best fit to this information but am unsure of how to do this as the power option will not let me enter 0.5.

I'm using excel 2007

Thanks in advance for any help

No luck finding an answer? You could always try Google.