Solved

Excel Formula battles

Posted on 2013-01-21
5
258 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:
Ingeborg Hawighorst 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
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 create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

839 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