Solved

Excel Formula battles

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
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.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

733 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