Solved

Excel Formula battles

Posted on 2013-01-21
5
249 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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

786 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