Has percentage gone up or down based on prior cells with percentages in

[moneypennie21]

Hi I am trying to add a column to a spreadsheet to show if results have improved or not based on a percentage. However there are certain results which are NA (This is because a specified one of the values is 0 and so is an expected result). However as some of the columns go from NA to 0% (this happens if the specified value is greater than 0 but the other value is 0). some of the columns go from NA to NA and some go from NA to xx% or from xx% to NA. if the values are as follows I need to reflect it in the movement column
Previous Month NA - Current Month 0% - up or down based on earliest month where a value was shown - if no value shown then can be NA
Previous month xx% Current Month NA - up or down based on earliest month where a value was shown - if no value shown then can be NA
Previous month xx% Current Month xx% up, down or no change based on these figures

if this could auto updated based on what month we are in as well this would be great!

I have attached example of what I mean with the formula I was trying to make work in column R - This is not currently doing what I need.

Thanks

[Kaper]

Hi,
My proposition is in R2 Array formula*:

Please Login or Register  to view this content.

If you want to look at it step by step see columns T:W in attachment, where building blocks were left :-)
Once you got it you can delete these columns. They are not used in real formula.

How to add data for May 2018 (same with next months:
1) copy current column R to column S (not move, no column insert, just copy - you will see the same results in both columns at the moment)
2) write header in R1 and current data in R2 down. And that's it :-)

*...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

[moneypennie21]

Hi Kaper, thank you for the response however the "up" "down" and "no change" don't actually seem to relate to the values? for example in row 2 it should be "no change" as it is from 0% to 0%. in row 4 it should be "up" as it goes from 13% to 27%

am I missing something?

Thanks

[Kaper]

Shame on me, of course I badly misplaced names in final part of formula. Shall read: "Down","No Change","Up" (for: minus sign result, zero result and plus sign result)

Please Login or Register  to view this content.

The same error is of course in formula in column W.

See corrected file

[moneypennie21]

Thanks Kaper, this works greta on your spreadsheet just having a slight issue transferring it to my original spreadsheet. I should be able to fix it though. thanks for your assistance.

[Kaper]

Thanks for the reputation point.

The way to go with transfer is to adjust model sheet (add columns/rows, etc.) to excactly follow original layout and only then copy the formula text.
Just a reminder - commit as array formula (with Ctrl+Shift+Enter).

Once it's fully working please remember to mark thread signed :-)

[moneypennie21]

Hi Kaper, I have identified what is causing the issue but I am not sure how to fix it. in the sample data I attached I only had one column ahead of the monthly columns with account type in. In the original data I have several more columns in front of that. basically my source data goes -
Branch Code, Branch Name, Branch Name and Number, Branch Banding, Branch Acquisition Date, Month, Year, Region, Area Manager, Account Type

and then has the monthly columns after all of these. when I delete these extra columns and have the account type in column A the formula works as it should. I have realised that this is because the formula is picking up the Month and Year because they are numbers it is calculating using these. however if I change the column values in the formula to start at column J (which is the column which has the account type in as in the example data I sent) it doesn't work anymore. any idea why this might be?

Thanks

[Kaper]

I do.

But why don't you just post a small sample (no real data).

If leftmost "header" columns contain only texts do not modify formula. Even if data starts from column say K leave formula with ranges starting in column A as:

Please Login or Register  to view this content.

Adjust only last column.

But probably it is not the case, so formula shall be adjusted. As I wrote - easiest way will be on sample file with meaningless data (but formatted, and placed in a sheet like real data is.

[moneypennie21]

Hi, Please see the attached sample data. As you can see I have amended the formula to start at column J but it no longer works and if it starts at A it works for up, down and no change but if all the values are N/A as in row 16 it shows UP - as it is taking into account the month and year in columns F & G. Now I have removed these columns in my original source data to make it work but I would like to try and understand why changing the start column does not fix this error if possible?

Thanks

[Kaper]

As we are adjusting formula, I removed any header from it so it starts from column K. The adjustment is by deductuing number of columns with description (A:J)
if it's always A:J one could use simply 10 instead of (both) COLUMNS($A$1:$J$1)

Please Login or Register  to view this content.

[Kaper]

PS. Try also simplified version (array formula of course):

Please Login or Register  to view this content.

In Indexes there is column A, while in Isnumber and Column we start from K

[moneypennie21]

Thank you so much for this, both of these work great and help me understand it a bit better. really appreciate your patience on this.