Variance formula - is this correct?
 

I need a formula that I can use with conditional formatting to highlight
cells that vary from the Mean by more than (n*StdDev), where "n" is a value
the user enters in B22.

The data table is laid out so that each row contains a series of numbers
that represent a particular test result. The test values for each date are
in the horizontal rows and there are 20 different tests (20 rows) each with
it's own Mean and StdDev; and there are 14 different test values for each
"Date of test", so the last column is "Q"

I think the formula I want is VARP:

=VARP($B2, D2) ($B$22*$C2) - if this evaluates to true, apply formatting.

The Date of each test is in column "A",
the Mean (average) is in column "B",
the StdDev is in "C",
and Row 1 is a header row.

A | B | C | D | E | ... Q |
Date | Mean | StdDev | Data | Data | ... Data |
.... more rows ...
B21 - last row with data
B22 - user enters number here.

So if the user enters "3" in B22, formatting (highlight) would be applied to
all cells in range D2:Q20 that have a test value that varies from the Mean
by more than (3*StdDev). Each row has it's own Mean and StdDev, so each
row's formatting is calculated on *that* row's Mean and StdDev.

Is this correct? I've been looking at this way too long and could use a
sanity check...

Thanks in advance.

Variance formula - is this correct?
 

If your data can be either positive or negative you might consider (using row
2, cell D2 as an example) comparing:


ABS(C2*$B$22-B2)
with
ABS(D2-B2)

and copying the formula down and across
--
Gary's Student


"deko" wrote:

I need a formula that I can use with conditional formatting to highlight
cells that vary from the Mean by more than (n*StdDev), where "n" is a value
the user enters in B22.

The data table is laid out so that each row contains a series of numbers
that represent a particular test result. The test values for each date are
in the horizontal rows and there are 20 different tests (20 rows) each with
it's own Mean and StdDev; and there are 14 different test values for each
"Date of test", so the last column is "Q"

I think the formula I want is VARP:

=VARP($B2, D2) ($B$22*$C2) - if this evaluates to true, apply formatting.

The Date of each test is in column "A",
the Mean (average) is in column "B",
the StdDev is in "C",
and Row 1 is a header row.

A | B | C | D | E | ... Q |
Date | Mean | StdDev | Data | Data | ... Data |
.... more rows ...
B21 - last row with data
B22 - user enters number here.

So if the user enters "3" in B22, formatting (highlight) would be applied to
all cells in range D2:Q20 that have a test value that varies from the Mean
by more than (3*StdDev). Each row has it's own Mean and StdDev, so each
row's formatting is calculated on *that* row's Mean and StdDev.

Is this correct? I've been looking at this way too long and could use a
sanity check...

Thanks in advance.

Variance formula - is this correct?
 

A slight correction: compare
ABS(C2*$B$22)
with
ABS(D2-B2)


--
Gary's Student


"Gary's Student" wrote:

If your data can be either positive or negative you might consider (using row
2, cell D2 as an example) comparing:


ABS(C2*$B$22-B2)
with
ABS(D2-B2)

and copying the formula down and across
--
Gary's Student


"deko" wrote:

I need a formula that I can use with conditional formatting to highlight
cells that vary from the Mean by more than (n*StdDev), where "n" is a value
the user enters in B22.

The data table is laid out so that each row contains a series of numbers
that represent a particular test result. The test values for each date are
in the horizontal rows and there are 20 different tests (20 rows) each with
it's own Mean and StdDev; and there are 14 different test values for each
"Date of test", so the last column is "Q"

I think the formula I want is VARP:

=VARP($B2, D2) ($B$22*$C2) - if this evaluates to true, apply formatting.

The Date of each test is in column "A",
the Mean (average) is in column "B",
the StdDev is in "C",
and Row 1 is a header row.

A | B | C | D | E | ... Q |
Date | Mean | StdDev | Data | Data | ... Data |
.... more rows ...
B21 - last row with data
B22 - user enters number here.

So if the user enters "3" in B22, formatting (highlight) would be applied to
all cells in range D2:Q20 that have a test value that varies from the Mean
by more than (3*StdDev). Each row has it's own Mean and StdDev, so each
row's formatting is calculated on *that* row's Mean and StdDev.

Is this correct? I've been looking at this way too long and could use a
sanity check...

Thanks in advance.

Variance formula - is this correct?
 

A slight correction: compare
ABS(C2*$B$22)
with
ABS(D2-B2)

10-4.

Thanks for the reply.

I think the best thing to do is just look at the results using test data.