Using sumproduct gives 0 when using indirect directly in the formula

Hello

Ofcourse!
Data starts in A7 but unfortunately is formatted wrongly so there is a bunch of correcting those data values.
The real data I want to use starts in cell Q7. One array is always based on row 6 (but the number of elements can change) and the other array in the sumproduct formula changes every row ofcourse.
I expect that this should be enough data :D

Everything is as asked:
1. Done
2. Done: column Y is the perfect example of what I want but without using columns W and X
3. Done
4. Not using them anyway ^^

Hi Glenn!

Thank you for your reply.
Honestly, I don't think that it is none of your concern what I am doing in the Excel file regarding mathematics. This also implies that you don't know what I am doing and what I want to achieve, which is again, not important for you.
This is not a matter of mathematics. I know which elements I have to multiply, divide, sum ... to get where I want. What I don't know is exactly that which I posted initially and which is 100 % Excel related.

I hope that you are able to see past all those things and actually focus on what is important: column W, X and Y. I don't want to use cell references in column W and X but I want to directly inject the cell references in the formula in column Y, which results in 0 if I do so for every cell.

I am still looking forward to a reply/solution for this problem.

If you still think that this not Excel related, then please don't bother responding. It won't benefit anyone on this forum and I found it rather rude and disrespectful.

Kind regards

Your post does not comply with Rule 3 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

Please make sure to include the full link, so that other members will be permitted to continue helping you

Note that if you have fewer than 10 posts, you may need to type the link, or past it in pieces (or ask a mod to do that for you)

Hello Fluff13!

Since you mentionned it on the other forum (Fluff?) as well (and actually added the link), would you please be so kind to do that here as well (tried it 2 times but got denied because of low number of posts). Also read the article about cross-posting, I can see why this might become an issue. Had no idea that forum experts were active on many different forums at once so totally get why this could become an issue.
I'll remember this for future threads.

Back to Glenn:

I wouldn't quite understand why I would ignore it. Yes, you were a bit harsh on me but this might have been because you were going through the file too fast and perhaps didn't read what I was searching for and this got us in a misunderstanding.
First of all, thank you for your solution.
Second: would it be possible to explain in detail how you constructed that formula(s)? I've never seen/used the aggregate function for example or the column function based on the entire first column? Also the sumproduct functions are quite odd since I'm used to just 2 arrays normally. I don't tend to or want to build the most "performant" or "clean" function to be honest. I want to build a formula that I understand 100 % in each component so I can reuse and rebuild it for other purposes. I have absolutly no idea how I could reuse this one for other cases because I don't understand quite a bunch of the building blocks (not regarding the mathematics behind it).
Unfortunately you also made no distinction between outliers and "-" values (I know I didn't specifically ask for this, but it did work in my sample ...) so I can't see how many outliers there are anymore. Changing "" to "N/A" won't help me since it also turns "-" to "N/A" in the if.error formula I assume.
I hope that you aren't frustrated or mad at me for my reaction and that you are willing to take the time to explain this to me.

Thanks in advance (again)!
Kind regards

The cross post link is
https://www.mrexcel.com/forum/excel-...s-0-value.html

Thank you very much Fluff13!

Also additional note for Glenn:
I noticed that you have made the a value change signs? I can't see why you did this? Omitting the - at the start of the formule right before the sumproduct. This also immediatly gives me correct values for b as well (the values that I had in my sample file). I have no idea if it can help you but I can't find out why you did it so ...

Kind regards!

Hi again!

Well there has been quite a misunderstanding then since I thought that you meant something completly different with your last sentence in that specific post ("Forget Excel" and "maths, not Excel, please ..."). I got the impression that you thought I asked a maths question and expected an Excel question so I don't know. So I was frustrated (and I assumed you were as well in your post).
My objective is to make a linear regression (without the formula that is designed for it) it the components a and b according to Y = ax + b. The solution to this line that minimizes the error term is quite obvious but is rather annoying to split in multiple cells. It's much cleaner (I think it is) to get those in 1 cell only.
Unfortunately, I made the export in reverse order. The datapoints had to be reversed so the regression line went in the right direction (I could have just copy pasted the columns a few times or something like that, but I like using inefficient formulas to do what I wanted).
I then needed to make abstraction for myself if certain datapoints were outliers. I calculated them because I marked them with N/A while missing points were marked with "-" and I could make the distinction quite easely.
Then, I had to rearrange the datapoints to omit the N/A values and the "-" values so there were no gaps in the data (or if I changed the sigma for the outliers, the regression lines would update automatically). I understand that you have done this using index and aggregate? I did it using indirect references which worked for me (again not looking for the most efficient or performant formulas).
And then I had to calculate the actual a and b I was looking for. But I had to find a way to implement the sumproduct formula without the actual cell references calculated in 2 cells, I wanted that to all be in 1 cell only. So this is the problem that I had, which you solved but in a way I can't understand. So yes, it could be beneficial to know what I intended to do, even though I didn't ask for a cleaner way to do all of this (but you did and now I would like to get to know this aggregate construction). What I certainly would (still) like to know is: how could I have made it work with the cell references (indirect)? Yes, your formulas all work fine and give me the desired results, but as you probably have read before, I'm not looking for the perfect formula.
The next step is to use these steps to calculate a weighted linear regression using array formulas and things like that. I have the solution for this, but it will use 3 arrays and not 2 so I really need to understand your formulas in order to start or try to make this type of regression.

I hope that this explains why I reacted in the way I did. I would like to apologise for that and I hope that you are still willing to give me these additional details/information.

Kind regards

Haha, enjoy yourself and have a great evening!
See you tomorrow!

Hello Glenn

So first off all, hats off to you for the very neat looking Excel file.
I like the concern for the outliers and stuffs. I indeed messed something up with that stdev. I know those excel formulas but decided to not use them (idk why) and I didn't validate the outcomes (a "-" gives a 0 because it can't be calculated correctly using my formula and stdev ignores it). After changing this, I see that the other formula is also messed up a bit since it doesn't do what I wanted it to do.
I still don't want to use the Intercept and Slope formulas since I need to go to a weighted regression and that I want to make it work with the actual solutions to those optimizations. But I got it to work with offset functions (still figuring out your initial formulas so idk).

Then there are 2 mathematical questions or assumptions that you can make, depending on what is required.
First off all: outlier detection and omitting outliers.
1. Only outliers above a specific threshold should be omitted which is why I only test for items above the treshold of mean + SD*2. SD is based on n values and not (n-1) since this would mean the treshold would be higher which I didn't want. Ofcourse you can use (n-1) as well and just decrease the value of 2. Note that this is not meant as a statistical correct test nor is this for research purposes so I'm definately not worried about this. Normally you would perhaps assume normal distributions and alpha values of 2.33 or 2.575 but that is all out of the question.
2. What you are doing is omitting outliers but there is still a gap. This means that the values that come after the outlier, have a higher "weight" in the regression. I didn't want this to happen. If there is an outlier, this value is treated as completly omitted, as if the value wasn't there in the first place (you can then say: don't you have to calculate the SD again and everything after omitting but that is a bit meh ...).
The difference is that I expect the next value (as in forecast) to be smaller than what you are calculating.
Example:
X: 1-2-3-4-5-6
Y: 11-21-31-400-51-61
Regression according to you: Y = 10X + 1
The next X value would be 7 and therefore: Y = 10*7 + 1 = 71
My regression would be based on:
X: 1-2-3-4-5
Y: 11-21-31-51-61
Therefore: Y = 13X - 17
The next X value would be 6 and so we get: Y = 13*6 - 17 = 61
I can imagine that the way I use this might upset you since this is a very unorthodox way of calculating forecasts and stuff like that.

It also has to be quite simplistic so I don't need statistics (I've had my fair share of them and I know how to use them, they are just not required for this kind of calculations).

I guess that I've covered everything that you asked/explained so feel free to comment on my view on this. Do you have a better way for outlier detection on such small data samples without going into statistical mumbo-jumbo? Feel free to let me know Just bear in mind that I am still a student actually so don't start with very complex calculations or matrices because that is most probably not required for this, or for what I need.

Kind regards and glad you provided me with this excellent information and help!

Hi Glenn

No problem
Data is regarding sales/purchasing so don't worry about outliers or methodology etc ...
Especially since you can't forecast or model this type of sales due to the high number of elements of which a lot are unknown.
If you have a nice approach for this, just let me know!

Thanks already for all the previous things you've done or explained. Helped me a lot!

Kind regards