Complex Heat Map using conditional formatting

[Corried]

Good day excel world.

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

Consumer Confidence - Countries - List

This page displays a table with actual values, consensus figures, forecasts, statistics and historical data charts for - Consumer Confidence. This page provides values for Consumer Confidence reported in several countries. The table has current values for Consumer Confidence, previous releases...

tradingeconomics.com

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

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

Albania Consumer Confidence

Consumer Confidence in Albania increased to -25.04 points in October from -25.25 points in September of 2024. This page provides - Albania Consumer Confidence - actual values, historical data, forecast, chart, statistics, economic calendar and news.

tradingeconomics.com

and

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

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

Argentina Consumer Confidence

Consumer Confidence in Argentina increased to 42.42 points in October from 39 points in September of 2024. This page provides the latest reported value for - Argentina Consumer Confidence - plus previous releases, historical high and low, short-term forecast and long-term prediction, economic...

tradingeconomics.com

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.

What do you suggest I do?

 

[AlanY]

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]

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]

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]

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]

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]

you can re-download heat2 example , I did ABS for column Last

 

[Corried]

Here is an example of a heat map

TRADING ECONOMICS | 20 million INDICATORS FROM 196 COUNTRIES

View more than 20 million economic indicators for 196 countries. Get free indicators, Historical Data, Charts, News and Forecasts for 196 countries.

tradingeconomics.com

 

[svendiamond]

Corried, use the ABS formula like literally type =ABS(A1) which would change cell A1 to a positive number

 

[Corried]

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