LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 62
Default 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.


 
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
#DIV/0! in variance/percentage formula Annette[_3_] Excel Discussion (Misc queries) 3 September 4th 09 11:54 PM
Pivot Tables - Variance and Variance % PJS Excel Discussion (Misc queries) 2 January 18th 06 03:12 AM
Formula for calculating Variance Lori Lynn Excel Discussion (Misc queries) 2 July 25th 05 08:41 PM
Excel % Variance Formula........ BigDon Excel Worksheet Functions 5 June 7th 05 08:58 PM
Pivot Tables - Variance and % Variance fields CraigS Excel Discussion (Misc queries) 5 January 6th 05 12:22 AM


All times are GMT +1. The time now is 04:44 PM.

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

About Us

"It's about Microsoft Excel"