Complex Heat Map using conditional formatting

Corried

Board Regular
Joined
Dec 19, 2019
Messages
79
Office Version
2019
Platform
Windows, Web
Good day excel world.

I have a concern.
Below link have a list of countries that carries each country unique data.

Now let's take the 1st two example:
#1 Country: Albania Data: -13.60
Below is Albania historical values snapshot:
Albania.PNG

If you notice all there historical data are measure with a (-) number.


and

#2 Country: Argentina Data: 42.40
Below is Argentina Historical values snapshot:
Argentina.PNG

If you notice all there historical data are measure with a positive number.


My problem is this:
How can I use 3-Color Scale "Heat map", using conditional formatting, to show it correct color code for each country?

Below is the WRONG EXAMPLE of conditional formatting use directly.
heat (1).PNG


What do you suggest I do?
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
3,979
Office Version
365, 2019, 2016
Platform
Windows
create an additional column, add the max -ve numbers to all thus create a column with only positive numbers.
and use this column to CF the real data
 

Corried

Board Regular
Joined
Dec 19, 2019
Messages
79
Office Version
2019
Platform
Windows, Web
create an additional column, add the max -ve numbers to all thus create a column with only positive numbers.
and use this column to CF the real data
Hummm. Thanks for your swift reply.

I am sorry but how am I going to do that?
 

Corried

Board Regular
Joined
Dec 19, 2019
Messages
79
Office Version
2019
Platform
Windows, Web
create an additional column, add the max -ve numbers to all thus create a column with only positive numbers.
and use this column to CF the real data
AlanY. Hello. Please point me to the right direction. I am new to excel.

What do you mean by "Max-ve" and "CF"?
How am I going to get this done?

Thanks in advance
 

svendiamond

Well-known Member
Joined
Jun 13, 2014
Messages
1,466
Office Version
365
Platform
Windows
He probably meant to say get the absolute value. The ABS() function will change them all to positive numbers. Use that for your conditional formatting.
 

Corried

Board Regular
Joined
Dec 19, 2019
Messages
79
Office Version
2019
Platform
Windows, Web
He probably meant to say get the absolute value. The ABS() function will change them all to positive numbers. Use that for your conditional formatting.
Thank you for your reply. Please. I am embarrass already. I have understood what you are saying.
The problem is, I do know how...?
If you are willing. What are the steps I need to make it work?

Thanks in advance
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
4,187
you can re-download heat2 example , I did ABS for column Last
 

svendiamond

Well-known Member
Joined
Jun 13, 2014
Messages
1,466
Office Version
365
Platform
Windows
Corried, use the ABS formula like literally type =ABS(A1) which would change cell A1 to a positive number
 

Corried

Board Regular
Joined
Dec 19, 2019
Messages
79
Office Version
2019
Platform
Windows, Web
Corried, use the ABS formula like literally type =ABS(A1) which would change cell A1 to a positive number
I got you. The problem is: some countries carries both negative and positive numbers while, a few carries only negative numbers. If I change all to positive. How that is going to help me move forward?

Thanks in advance
 

Forum statistics

Threads
1,085,156
Messages
5,382,027
Members
401,767
Latest member
JohnLeek

Some videos you may like

This Week's Hot Topics

Top