Percent change in this array is not tracking correctly.
In the attached file, the change in percent is not calculating correctly in the array. For example,
First, in the Baby Bear extract, the percent of change from row 7 to row 6 is only shown as 1%
Second, the percent is not showing as negative when the volume decreases.
Third, I'm not sure the correct formula for percent change is accurate:
For Percent Change:
New number - Original number = Difference
Difference / Original number = Answer
Answer x 100 = Percent Change
The "times 100" part might not be necessary, since we are using the percent number property in the Change in Percent column cells.
Disney.xlsx
First, in the Baby Bear extract, the percent of change from row 7 to row 6 is only shown as 1%
Second, the percent is not showing as negative when the volume decreases.
Third, I'm not sure the correct formula for percent change is accurate:
For Percent Change:
New number - Original number = Difference
Difference / Original number = Answer
Answer x 100 = Percent Change
The "times 100" part might not be necessary, since we are using the percent number property in the Change in Percent column cells.
Disney.xlsx
David Johnson, CD
Is change in volume the current # of records? If it is instead the difference between the previous date and the current date you don't show the change as being negative
ASKER
Hello David. Right, it is the difference in volume between the previous date and the current date. So, if the volume went from 100 to 70, the percent change would not be negative? I suppose I can see it as being strictly a percentage of the change. I suppose I was thinking that instead of having a percent increase formula and a percent decrease formula, a negative change for the percent increase formula would just indicate that there was a decrease.
"is change in Volume" correct?
ASKER
Hello Kurt, Yes, "Change in Volume" is calculating correctly.
I can't read the format of the percent formula in the array. Would you please put in plain English what the formula is, similar to how I posted a formula, above?
I can't read the format of the percent formula in the array. Would you please put in plain English what the formula is, similar to how I posted a formula, above?
this is formula, after removing if/error part
you can use
instead I guess... it will give
=INDEX(Records,ROW(B4)-3)/INDEX(Records,SMALL(IF(Extract=B4,ROW(Extract)-ROW(INDEX(Extract,1,1))+1),COUNTIF($B$4:B4,B4)+1))/100you can use
=IFERROR(1 - IF(D4>0, (C4-D4)/C4, C4 / (C4-D4)),"No Previous Record")instead I guess... it will give
0.10%
35.20%
30.88%
14.44%
0.06%
0.29%
No Previous Record
24.71%
0.08%
12.11%
0.08%
38.80%
0.06%
No Previous Record
No Previous Record
No Previous Record
No Previous Record
No Previous Record
or this one
which gives (after changing format to % and setting it to 2 decimal)
=IFERROR((1 - IF(D4>0, (C4-D4)/C4, C4 / (C4-D4))) * SIGN(D4),"No Previous Record")which gives (after changing format to % and setting it to 2 decimal)
0.10%
35.20%
-30.88%
14.44%
0.06%
0.29%
No Previous Record
-24.71%
0.08%
12.11%
0.08%
38.80%
0.06%
No Previous Record
No Previous Record
No Previous Record
No Previous Record
No Previous RecordASKER
Hello Kurt,
The formula you provided in column J of the spreadsheet, is what I was looking for. Would it create a problem to have this non-array formula in the same worksheet that has the array formulas in the other columns?
Records are continuously added to the bottom of the table and then resorted in Date order, New to Old. Not knowing, I just want to be sure that the table will adjust to both types of formulas naturally, when adding new records.
The formula you provided in column J of the spreadsheet, is what I was looking for. Would it create a problem to have this non-array formula in the same worksheet that has the array formulas in the other columns?
Records are continuously added to the bottom of the table and then resorted in Date order, New to Old. Not knowing, I just want to be sure that the table will adjust to both types of formulas naturally, when adding new records.
no it will not affect...
that formula uses some cells on the same row which already using those strange formulas which is hard to understand :)
that formula uses some cells on the same row which already using those strange formulas which is hard to understand :)
ASKER
I failed to properly define what I meant by change in percent. It should say, Percent Change.
On 7/7 Mama Bear had 15,720 records
On 8/11 Mama Bear had 24,261 records
24,261 (new) - 15,720 (old) = 8,541 records
8,541 / 15,720 = 54% increase
Therefore, on August 11, the percent change should be 54%. This also matches the formula I originally requested.
On 7/7 Mama Bear had 15,720 records
On 8/11 Mama Bear had 24,261 records
24,261 (new) - 15,720 (old) = 8,541 records
8,541 / 15,720 = 54% increase
Therefore, on August 11, the percent change should be 54%. This also matches the formula I originally requested.
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 Kurt. They way you boiled the percentage calculation down to it's simplest form was brilliant. I kept fighting it until it finally made sense.
For continuity, this is a separate but related question to: https://www.experts-exchange.com/questions/29048271/How-do-I-track-the-volume-difference-percent-change-and-date-changed-in-Excel.html
For continuity, this is a separate but related question to: https://www.experts-exchange.com/questions/29048271/How-do-I-track-the-volume-difference-percent-change-and-date-changed-in-Excel.html