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.