Conditional formatting for top 25 referrals

[bjbboats]

Hi

I have two tables for a web report. They are YTD 2015 and 2014 for top 25 referrals (source).

I'd like to include an arrow up or arrow down if they have moved compared to last year's data.

Does anyone know of a formula for this? i have 1-25 on both tables and the source name which should match the 2014 table.

Thanks,

[Nisha Dhawan]

Hello bjbboats,

i have attached the file for your reference.

kindly confirm if this is what you are looking for.

[bjbboats]

Hi thanks for getting back to me.

I've attached a spreadsheet to demonstrate what I mean.

Thanks for the help!Conditional formatting.xlsx

[Nisha Dhawan]

Hi please convert Vlookup formula in values and then attach Excel file again

[bjbboats]

The data is sorted by revenue. I've put some example stats in.

There will also be the scenario where it is a new referral for 2015 so there will be no comparison to 2014. Is it possible to set that as a yellow flat arrow?

Thanks Conditional formatting v2.xlsx

[Nisha Dhawan]

Hi bjbboats,

Please refer to the attached file and hope this time it will solve your problem.

As required, in case of any new scenario (new referral for 2015) will be set as yellow flat arrow.

[bjbboats]

Hi Nisha

Thanks for all the help. How I need it to work though is even if zqz for eg has the same revenue I still need it to have an arrow up because it's moved up the table. Does that make sense? I've attahed the full table to give you some more info

Thanks

Ben Conditional formatting v3.xlsx

[Glenn Kennedy]

Try this. I thought that it was position number you were after...

[bjbboats]

is it possible to do it with conditional formatting so it includes coloured arrows?

Thanks

[ashishmehra2010]

Hi Nisha

Thanks for all the help. How I need it to work though is even if zqz for eg has the same revenue I still need it to have an arrow up because it's moved up the table. Does that make sense? I've attahed the full table to give you some more info

Thanks

Ben Attachment 390565

Hi Ben,

Can you define the logic behind creating a formula to show UP arrow via CF even if both the values are same.

Regards,
AM

[adhawan06]

is it possible to do it with conditional formatting so it includes coloured arrows?

Thanks

Hello there - Try the attachment. I have just made small changes in Nisha Dhawan's answer.

Also, note i have made the changes only in column D. Please confirm if this works for you then you can same consider in the column L.



Anil Dhawan

[bjbboats]

Hi Anil

Thanks for the response. Does this formula indicate if revenue has gone up or down? Is it possible to have another column in the 2015 table that pulls through last years position in the top 25 list?

Kind regards,

Ben

[bjbboats]

Hi Anil,

Actually thinking about it is it possible to create arrow formatting to represent how the referral has moved in the top 25 compared to last year? so if it's moved from 4 in 2014 to 1 in 2015 have an arrow up?

And is it possible to have a revenue difference (2015 - 2014)?

Many thanks

[adhawan06]

Hi Ben,

What I have understood from your last post is that you are looking to have ranks for different year and then you wanted to see whether the current year's revenue has been increased and what is the current rank.

For e.g.

If AAA's revenue in 2014 was $30,000 and ranked 4th and in 2015, it has earned $31,000 and also ranking at 2nd. So you want to see up arrow with the rank +2 up. Correct my understanding so that i may help you with the solution.

[Glenn Kennedy]

Anil, I think that it's the ranking POSITION that the OP wants compared year on year, not the monetary value.

[adhawan06]

Yeah you are right even I am thinking for the same but waiting for OP confirmation. Maybe once op confirms then only I will go ahead

[bjbboats]

Sorry for the confusion. it's changed a bit.

I'd like a column which shows if it's moved up or down in the top 25 list from the previous year.

I'd also like a column which shows the position it was in the previous year

And lastly a revenue difference (2015 rev - 2014 rev)

Thanks for all the help

[bjbboats]

Thanks Any I've replied

[adhawan06]

Hi bjbboats,

Sorry for late.

Please refer to the attachment. As per your last to last post, I have included 3 columns highlighted in "Dark Orange colour". I hope this meets your exact requirement if not, feel free to let me know.


Cheers!!!

Anil Dhawan

[bjbboats]

Hi Anil

Apologies I've just started working with this. Thanks again for all the help!

The revenue difference seems to be working fine but I'd like the arrow up or down to look at the rank number it is in the table (1-25) not sure if it's doing that now? And the same with Current rank v previous rank. Is it possible to set it up so it looks at the rank number?

[Nisha Dhawan]

Hi bjbboats,

You can change the Icon just go in conditional formatting> manage rules> change the icon as showing in below image:-

Conditional format.PNG

[bjbboats]

Thanks Nisha but the forumla's don't seem to be behaving correctly for me.

[adhawan06]

Sorry for the confusion. it's changed a bit.

I'd like a column which shows if it's moved up or down in the top 25 list from the previous year.

I'd also like a column which shows the position it was in the previous year

And lastly a revenue difference (2015 rev - 2014 rev)

Thanks for all the help

I guess whatever you had asked for, I have given you the same in the sheet.

Cond Format.PNG

The red highlighted area is showing you whether the column has been moved up or down in the top 25 list from the previous year. The green arrows are in up and reds are in down.

The Blue highlighted area is showing you the position for current year (previous) year. for e.g. Source "ABC" has revenue 28,000 in 2015 and ranking at 1st, and in 2014 it was ranking at 2nd with 26,000 Revenue.

I am confused that what you are looking for. I apologies if I am unable to understand your query so please make me understand one more time with example if this is what you are not looking for.

Thanks,
Anil Dhawan

[bjbboats]

Does a fuller table help to illustrate it better?

[adhawan06]

Can you also include the columns you looking for with expected result because i still thinks that i have given the required solution. Please???

[bjbboats]

I think you probably have too but I can't figure out how to implement it into the table. apologies! New doc attached

[samba_ravi]

see the attached file

[bjbboats]

Guys this is great thanks so much! Is it poss to space out the current position vs last years position or even separate them into different columns?

[adhawan06]

Hey Bjbboats - this is what exactly i have given it to the only thing is that i have placed in different columns as you haven't mentioned in which column you were looking for.

Anyways just use the below formula to add space before last year position.

Please Login or Register  to view this content.

Also, if your query has been resolved, please mark this thread as SOLVED and say thanks to people who helped you by adding *Add Reputation.

Cheers!!!

Anil

[bjbboats]

Thanks Anil. I will do. Great help!!

[bjbboats]

One more question on the formula for the position. If there isn't a referral to compare to last year is it possible to include something in the formula which inputs the position it is in 2015? Currently I have gaps where there isn't a comparison to last year.

Thanks,

[adhawan06]

Hi Bjbboats,

It will be really appreciable if you can mark this thread as SOLVED now, and open a new thread with a new requirement. As the answer for your original question has been given to you and this seems to be a new requirement.

Cheers!!!

Anil Dhawan