Excel formula for fluctuation
Excel formula for fluctuation
the spreadsheet screenshot below represents the number of articles(items) that a company sells every month.
I would like a formula that calculate the growth (positive or negative) of the sale.
Thank you
the spreadsheet screenshot below represents the number of articles(items) that a company sells every month.
I would like a formula that calculate the growth (positive or negative) of the sale.
Thank you
ASKER
Why it factors in only the first and last month
Because you didn't describe what math you actually want to use. Do you have something else in mind?
»bp
»bp
ASKER
for the sake of simplicity I put just 4 months...
we can see the sale for Art1 ,Jan were 1254 then went up 3015 then came down 100 and down 96
we can find the average sale during the last 4 months, but I believe the growth should be compared gradually, between Jan and Feb then Jan+Feb compared to March, then Jan+Feb+March compared to April, that way we can find the growth...
That's what in my mind..
we can see the sale for Art1 ,Jan were 1254 then went up 3015 then came down 100 and down 96
we can find the average sale during the last 4 months, but I believe the growth should be compared gradually, between Jan and Feb then Jan+Feb compared to March, then Jan+Feb+March compared to April, that way we can find the growth...
That's what in my mind..
How about showing it graphically rather than as a % Figure?
A line or bar chart will show each month side by side so can see the trend up/down.
A line chart could be used to show the cumulative sales to date, the angle of the slope would indicate growth %, a steep line is high growth, a shallow line is slower growth, a horizontal line is no growth.
A line or bar chart will show each month side by side so can see the trend up/down.
A line chart could be used to show the cumulative sales to date, the angle of the slope would indicate growth %, a steep line is high growth, a shallow line is slower growth, a horizontal line is no growth.
ASKER
I might agree though that the growth is the Average sale during 4 Months..
Rob Henson: That's good idea to use graph for the fluctuation.
is there a way like when I select F2 , only Row 2 information is displayed with a Graph related to Row 2 shows up on the right side of Excel ?
That would be less cumbersome than having multiple graphs for multiple rows, that way it will show the fluctuation just for one Article
Rob Henson: That's good idea to use graph for the fluctuation.
is there a way like when I select F2 , only Row 2 information is displayed with a Graph related to Row 2 shows up on the right side of Excel ?
That would be less cumbersome than having multiple graphs for multiple rows, that way it will show the fluctuation just for one Article
So in your simple example, can you fill in the Growth % values that you want us to calculate for that example?
»bp
»bp
Copied the screenshot data into the attached file.
In cell A11 there is a drop down choice for Article, the chart on right hand side then reflects the monthly sales for the chosen article.
The formulas in columns F to M are deliberately forcing an #N/A result as this does not appear in the chart, if left with the basic VLOOKUP the empty months would show a result of 0 which would extend the line on the chart and "could" show a different picture to that required.
Sales-Chart.xlsx
In cell A11 there is a drop down choice for Article, the chart on right hand side then reflects the monthly sales for the chosen article.
The formulas in columns F to M are deliberately forcing an #N/A result as this does not appear in the chart, if left with the basic VLOOKUP the empty months would show a result of 0 which would extend the line on the chart and "could" show a different picture to that required.
Sales-Chart.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
THank you.. I will work on it sometime next week
If what I described, then just use a formula like this in F2 and copy down. Also change the format of the cells in that column to Percentage.
=(E2-B2)/B2
»bp