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 -26.25 points in March from -27.67 points in February 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 36.68 points in March from 36.04 points in February 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?

 

[Corried]

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

Ok. Will check

 

[sandy666]

so maybe Trading Economics - Analytics - Excel Addin

 

[svendiamond]

Well, what end result do you want? On the website you linked, with the heat map... It looks like they highlight red / green based on whether the metric is a good or bad thing. For instance look at Turkey. It's got one column with 11.25% and it's green, and the very next column is also 11.84% but it's red. So if you want to achieve the same result, you'd have to tell Excel which columns are good and which are bad... it's going to be a little more work, you know? Is this what you want?

 

[Corried]

so maybe Trading Economics - Analytics - Excel Addin

I just watch the video. I like the "Addin", if you are going to pay the monthly fee for me. Lol. Just joking. The monthly charge is to high for me. I'm trying to create my own with your support as well. Once you are willing...

 

[sandy666]

sure, have a nice day

 

[Corried]

Well, what end result do you want? On the website you linked, with the heat map... It looks like they highlight red / green based on whether the metric is a good or bad thing. For instance look at Turkey. It's got one column with 11.25% and it's green, and the very next column is also 11.84% but it's red. So if you want to achieve the same result, you'd have to tell Excel which columns are good and which are bad... it's going to be a little more work, you know? Is this what you want?

Yes Sir. Spider man and Batman are my superheroes. Just joking. This is what I'm looking for. How can I get around this?

Thanks in advance

 

[Corried]

sure, have a nice day

The day is nice over here as well... What do you mean?

 

[svendiamond]

Now that I think about it, it might be easier if you just go column by column, adding different conditional formatting as you go. Rather than try to make it so dynamic. Since I'm assuming this just a one time thing, right?

 

[svendiamond]

Just literally click on column A, and apply the conditional formatting either green to red, or red to green, depending on whether that's a "good" metric or "bad" metric. Then go to column B, do same thing. Keep going.

 

[sandy666]

I'm trying to create my own with your support as well

sure

can be: All the best