Solved

Excel Formula battles

Posted on 2013-01-21
5
226 Views
Last Modified: 2013-01-25
Kristine,

    Attached are 2 snapshots from an excel summary I am working on where the Summary page is looking at the Data page to determine line item selections and final outputs.  In the case of the summary, I am looking at a number of variables in the data (green background / data pg) to determine whether I should include the data from the column titled Order Change (last on right / data pg).  Once I have determined which are to be used, they are added to create a report summary.  

   With this, I have encountered two significant problems.
 1) Of highest priority:  Once I have identified the line items from Order Change column that I want, I am looking to statistically evaluate via the Standard Deviation for those items.  I tried evaluating the various STDEV tools, and found no workable answer.  

2)  For the second problem encountered, you’ll see on the Summary page, I have an Input Filter Level identified near the top of the pg where the input is set at $0.  I am currently evaluating a number of SUMIFS and COUNTIFS scenarios in my summary and these currently include reference to this $0 input.  Since this input is actually a variable that I’d like to change and evaluate, the current method of ‘hard coding $0’ into an equation requires very manual adjustment for each equation using the reference input.  I tried to incorporate a cell reference (like $D$2) to the location of the $0 input, but the xxxIFS equations seemed to fail no matter which format I tried.  Any help to automate this variable input into SUMIFS and COUNTIFS scenarios, is greatly appreciated.
GPVOUS-Only-Rev-4-TS-Data-Tab-1-.pdf
0
Comment
Question by:bergquistcompany
  • 3
5 Comments
 

Author Comment

by:bergquistcompany
ID: 38802960
0
 

Author Comment

by:bergquistcompany
ID: 38803831
Lets start with this one
1) Of highest priority:  Trying to get standard deviation off column AK of attached
that meet the criteria
…as example…

1)      If a Part  (P)  ….vs. a Sheet.
2)      If Customer is “1 – High” (Priority)
3)      If Eval (Column AH) is Yes…

Is this possible
Copy-of-Book2.xlsx
0
 
LVL 50

Accepted Solution

by:
teylyn earned 500 total points
ID: 38804603
Unhide the filtered column and remove the filter.

Create a helper column with the formula (assuming I have guessed the correct columns, since your description is not very precise, for e.g. Eval seems to be in column AI, not AH as you wrote)

=IF(AND(AE6="S",AF6="1 - High",AI6="Y"),AK6,"")

copy down to the last row of the unfiltered table. Then you can use STDEV.S  (Excel 2010) or STDEV (2007 and earlier) like this:

=STDEV.S(AL:AL)

cheers, teylyn
0
 

Author Closing Comment

by:bergquistcompany
ID: 38820961
Fabulous!!!!  Thank you so much for sharing your knowledge
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

This article will show you how to use shortcut menus in the Access run-time environment.
No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now