Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Excel Formula battles

Posted on 2013-01-21
5
Medium Priority
?
277 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 

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 (Microsoft MVP / EE MVE) earned 2000 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

Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

721 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