This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
In Excel, I'm looking for a conditional linest equation in column E and F (see below image) that give me the y-intercept and gradient of an array that is conditional upon a certain criteria (eg. bananas or oranges). For example, in the below circled screenshot, I want one equation to fill down that'll find the y-intercept/gradient value based on the relationship between price and days for either bananas or oranges (the values will repeat down for the same fruit - eg. bananas or oranges). This equation should stretch all the way down to row 15 so that it'll still work when additional data is added down, but it'll ignore the blanks for now. Thanks for any help!
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.
in E2 copied down:
=LINEST(FILTER($C$2:$C$11,$B$2:$B$11=B2),FILTER($D$2:$D$11,$B$2:$B$11=B2))
So nice when people say thank you, it really encourages you to help them again.
Hi,
I'm Sneha and I'd be happy to help you out with your question. Sorry for the inconvenience caused.
To find the y-intercept and gradient of an array that is conditional upon a certain criteria, you can use the LINEST function with an IF statement.
Assuming that the fruit type is listed in column A, the days are listed in column B, and the price is listed in column C, you can use the following formula in cell E2 to find the y-intercept for bananas:
=IF(A2="Banana",INDEX(LINEST(C$2:C$11,B$2:B$11^{0,1}),1),"")
This formula uses the IF statement to check if the fruit type is "Banana". If it is, the LINEST function is used to calculate the y-intercept and gradient for the data range C2:C11 and B2:B11^{0,1} (which creates an array with the values of 1 and the values in column B raised to the power of 1, which is equivalent to the values in column B). The INDEX function is used to return only the y-intercept value from the array. If the fruit type is not "Banana", the formula returns an empty string.
To find the gradient for bananas, you can use the following formula in cell F2:
=IF(A2="Banana",INDEX(LINEST(C$2:C$11,B$2:B$11^{0,1}),1,2),"")
This formula is similar to the previous one, but it uses the INDEX function to return the second value (which represents the gradient) from the array returned by the LINEST function.
To find the y-intercept and gradient for oranges, you can use similar formulas with "Orange" instead of "Banana" in the IF statement. Make sure to adjust the data range to include only the rows with "Orange" in column A.
Once you have entered the formulas in cells E2 and F2, you can fill them down to apply them to the rest of the data. The formulas will automatically update to include new rows when you add them.
For more Information, please refer to "LINEST function -" https://support.microsoft.com/office/linest-function-84d7d0d9-6e50-4101-977a-fa7abf772b6d
If you have any other questions or need assistance with anything, please don't hesitate to let me know. It will be my pleasure to Assist you.
Best Regards, Sneha