Probability dist

[IknowNOt]

Hello,

I need help to create a formula that shows me the % growth between 3 or 4 values.

Let us say that Value1: 10 and Value2: 20 and Value3: 30 how to calculate in one formula the growth between the 3 values? should I calculate each one and then divide by 3?

=(value2-value1/value1) is for the growth of the value 2 and 1. how to add the third value and see the overall growth between the three values?

Thank you

[Glenn Kennedy]

There's not enough information on the anticipated relationship between the values, so, a guess:

=SLOPE(A1:A3,ROW(A1:A3))/A1

[IknowNOt]

Hey Glenn,thanks.

Actually it is for giving a rough percentage on something I am getting each time and the numbers keep changing and I want to find a way to measure their growth or their changing percentate over the 3 weeks or 3 values. Say this week the value was 10. 2nd week value is 30 i know thats 200% growth but how can I add week 3 which is 10 again. So I need to track the fact that it went up from value 1 to value 2 then down in value 3.
I need help sorting this thing, this is why it is not clear.
Thanks again

[Glenn Kennedy]

OK. That's a step forward, at least. It sounds like the x-axis is measured in weeks (a linear distribution).

So if this (guess) isn't it... please put finger to keyboard and post a sheet showing what YOU expect to see...


=(LOOKUP(2,1/(B2:B20<>""),B2:B20)-B2)/B2

[IknowNOt]

Hi again,

I think your formula is what I am looking for. Can you please translate it for me? what does the actual final results mean? is it the average % of all growth?
or is it only comparing the final and the 1st? what I need is to consider the fact that the values are changing...your sheets is just perfect, thx again.

[Glenn Kennedy]

It's the difference between first and last... the LOOKUP bit returns the last non-blank value from the column.

If what you want to do is to compare the current situation with the starting point as a %, it's what you want. If the value starts from 10, goes to 1,000,000 and falls back to 10 again... the OVERALL change (first to current) is nil.

If that's what you want...

You're welcome. If not, I WILL need to see a sampple sheet from you.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please click on "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.

[IknowNOt]

testing live.xlsx

So I added an exemple, how can I know the way the growth has shifted??

Thx again...if you think there are a better way to see this, let me know. I am looking for the best way to observe how the values are changing

[MrShorty]

After a few hours, I will venture a comment.

This is beginning to seem to me to be less about Excel programming and more of a broader question about data analysis -- independent of programming language. If you can help us understand the desired calculation you want to take place in G2, we should be able to help you program that calculation. I don't think we are going to be as helpful at deciding what kind of calculation ought to go into G2.

That said, you indicate in the file that you want a single calculation that can communicate the up/down nature of the data sequence. I cannot think of any single value that can communicate that much information about the three values.

With only three values, I would be inclined to say that the three values themselves do the best job of communicating the trend. Of course, we usually simplify problems (to the point of oversimplification sometimes), so this might not be practical for a real set that may have dozens or more data points in the sequence.

I could conceive of using multiple values to describe the data sequence. Something using Glenn's SLOPE() function along with some measure of variance (like a correlation coefficient) could give me a sense of "direction" for the sequence (is it increasing, decreasing, or flat) couples with a sense of "noise" (is it up and down all over the place compared to the general trend, or is it fairly consistently following the trend). Are you required to come up with one, single quantity that can measure "up and down?"

Sometimes when I want to try to understand a sequence of data, a chart or graph is more helpful in helping to understand what is going on.

I really don't know what to recommend. Again, if you can help us understand the exact calculation you want to perform, we should be able to help you program that calculation in the spreadsheet. I'm not sure how much help we can be with the overall question being asked here.

[IknowNOt]

Hello MrShorty,

Thank you for your reply/comment. I am sorry if this seems out of excel, I was hoping to find help within excel, or find a formula for what I want.

Here is what I want in other words:

Say I want to make a decision about to buy or sell when the numbers go up or down. Say that the 1st week the number was 50 and the 2nd week is 100 I know that it's had 100% growth, right?

This info is perfect, but then again, we have the third week, where the number went down to 20, so the growth % has gone down. What I need is a way to figure out that it was up and now it went down. obv I am talking about more than just one value.

If this is beyond excel formulas, then please ignore it, but if you have some ideas, to navigate and get the best out of this, do let me know.
thanks

[MrShorty]

I don't think it is beyond what Excel formulas can do, I just find that I must first understand the math equations and algorithms and logic that I am wanting to use before I can express those equations or algorithms or logic in one or more spreadsheet formulas. When I talk about part of a problem being "outside of Excel," I am referring to those parts of the problem that come from financial and stock market analysts and strategists that tell me what quantities are useful in understanding a stocks performance and how those quantities might figure into my investment strategy. Since I am not a financial analyst of any kind, I have no idea what quantities or logic would go into deciding if this is a good stock to buy or sell. However, if I were to take the time and effort to ask financial analysts how they decide when to buy and sell, I am confident that, once I understood the quantities and logic they used, I would be able to come back to my spreadsheet and program those quantities and logical algorithms into my spreadsheet.

At this point, in order for us to help you program something into G2, we need you to help us understand the calculation and/or algorithm and/or logic that you want to use to build the formula in G2.

[IknowNOt]

Yeah, like I said what I need is:

I need help to create a formula that shows me the % growth between 3 or 4 values.

[MrShorty]

As you know, the general expression for "% growth" is %growth=(y-x)/x. This is the expression you are already using for your point to point changes in E2 and F2. Whatever you want in G2 should be the "same," just deciding how you want to calculate y and x in the formula you put into G2. At this point I have no idea how you want to calculate y and x for the %growth formula.

If you are fishing for ideas. Springboarding off of Glenn's SLOPE() function, you could use a linear (or other) regression to calculate y and x using those values. y=TREND(A2:C2,,3) and x=TREND(A2:C2,,1) and calculate the %growth based on a linear regression of the three points.

You could use a moving average to calculate y and x. y=AVERAGE(B2:C2), x=AVERAGE(A2:B2)

I'm sure there are other strategies. Again, I don't think we can choose a strategy for you, but if you will tell us how you would like to perform the calculation, we should be able to help you implement that calculation in the spreadsheet.