conditional array

I'm looking to predict data in pumping applications. given this. I will have unknow lengths of tables that will constantly have data added and removed. I can't find a way to structure the selected data the way I would like it to be. All data is expected to be quadradic, so my basic LINEST is...

I'm trying to VBA code a few calculations. As an Excel array (control-shift-enter) formula, it reads like {=TRIMMEAN(IF(B:B="X",C:C),H2)}, where it is looking at a set of groups (B:B = "X"; find rows that contain "X" in column "B") , then calculating the Trimmean function for column "C" in...

Hello all! New User here, so bear with me I'm working on a little pet project, attempting to parse out data from a large date range using smaller date ranges. The goal is to automatically get references given a date range that can be used for math in other cells. Most of this can be done with...

Hello I am trying to insert the R squared formula as follows: =RSQ(Known_ys, Known_xs) ONLY if row(i)= "YES" and ignore the rest of the data. For instance, the below should calculate RSQ excluding the rows where values in column C ="NO". I tried the following formula, but it didnt work: =...

Hi guys, I am using LINEST Array to calculate price elasticity and its related coefficients. For a multi-product portfolio. Y values: Monthly Price elasticity for each SKU X Values: Coefficitents for 5 items: Season (4 separate coefficients) Relative price level vs substitute products Right...

Hello! I'm working with the following section of a formula: SUM(SUMIFS('All Facilities'!$AC:$AC,'All Facilities'!$F:$F,$C$29,'All Facilities'!$AR:$AR,{"Operating","2017*"})) It's working just fine (and yes, entered with ctrl+shift+enter), but I want to make an adjustment to it: That last part...

Would be grateful for help adding a conditional filter to an array formula to find the maximum value up to a variable threshold in an unsorted list. I can do it for a whole list using help in other posts, but not for a conditional subset. Example shown below. For each department, I want to...

I have data (on a table aptly named 'Data') for region, department, sub-department and profit/loss. I've made a drop-down list in cells A1, A2, A3, which filter respectively for region, department, and sub-department. Each drop-down list includes an option for no filter - an "All" option. I'd...

Hi, I have a list of keywords in Sheet A (2000 rows) and I have a sheet B (column B), where I want to highlight Column B or get YES/NO (in nearby column D) if the cell of column B contains any keyword. For example - Column B cell contains "30secondMBA.com" and I have a keyword "MBA". If I...

Hello, I've learned a lot from people smarter than myself on this forum, so I'm here to learn again. Preface, I will try to be clear and concise, but there are several conditions and complications here: I have a table (~70 columns, 500 rows and counting) that is exported from a...

Summary Question I am trying to use LINEST to calculate the linear regression output for selected data points in a table. The selection of the data points is being determined by a conditional array. Here's an example of the formula: LINEST(IF(D2:D10000=2,E2:E10000),IF(D2:D10000=2,E2:E10000))...

I have a massive dataset and am preparing a dashboard based on this dataset. On my dashboard, I have a drop-down menu that allows me to select a month of my choice, from Jan to Apr. Here is a snippet of the data that I am looking to target my formula for. <code style="margin: 0px; padding: 0px...

Hi all, I have an existing cell formula : =ROUNDUP(L8-K8,4) I need to add a function so that if the value returned is a negative number, it equals "0". Not sure if i should combine in an "IF" statement or what... Thank you!

Hello, I have a database like this <tbody> Date Customer Item 1 Quantity 1 Item 2 Quantity 2 Item 3 Quantity 3 1-Jan-14 A orange 5 apple 8 banana 3 5-Jan-14 B apple 3 5-Jan-14 C banana 4 apple 2 5-Feb-14 A apple 6 8-Feb-14 A banana 4 orange 2 8-Feb-14 C banana 7...

I have a formula to find 2nd largest value in a range with dulicates =LARGE(IF(rangeB<LARGE(B2:B500,1),B2:B500),1)> < LARGE(</LARGE(B2:B500,1),B2:B500),1)>rangeB,1),rangeB),1) I also have another formula to find the second largest value given the condition that the =LARGE(IF(rangeA="cell...