From a long list (of items ordered by time) I want to calculate linear correlations depending on a condition (namely, for all items that belong to the same product). I am able to calculate the SLOPE and INTERCEPT with this formula:
{=SLOPE( IF( Data!A$2:A$1000=B1; Data!C$2:C$1000); IF( Data!A$2:A$1000=B1; Data!D$2:D$1000))}
(On the Data sheet, I have the product name in column A and the parameters to correlate in C and D)
However, I also need to know the uncertainties in the paramaters and / or predicted values. I want to use RGP for this. Unfortunately, RGP won't accept these arrays constructed with IF. I also tried to add NV() for the false cases (which SLOPE won't accept), but had no success. RGP always gives a #VALUE error.
Can anybody help me construct the correct input for RGP?
