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]

I am not clear what you mean: "Since I'm assuming this just a one time thing, right?"

However, Once the heat map is created? as I get new data in the future. the color codes should reflect the "true" value" of the country economic activities...

 

[Corried]

sure

can be: All the best

Hummm "Have a nice day", "All the best". I am sorry if I am wrong. It seems like you are wishing me well with out your support, It sounds like there are no way it can be done. I am not sure. Please set me straight...

 

[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?

How am I going to "tell excel which columns are good and which are bad" using this data: Consumer Confidence - Countries - List

 

[sandy666]

Hummm "Have a nice day", "All the best". I am sorry if I am wrong. It seems like you are wishing me well with out your support, It sounds like there are no way it can be done. I am not sure. Please set me straight...

that means: Everyone supports everyone on MrExcel

 

[svendiamond]

Corried, this is a forum for help with Excel, not help in understanding economic data. If you can't figure out whether the metrics you're trying to measure are good or bad, then I can't help ya :P

For instance, GDP increase year over year is a good thing. Debt increase is a bad thing.

 

[Corried]

Corried, this is a forum for help with Excel, not help in understanding economic data. If you can't figure out whether the metrics you're trying to measure are good or bad, then I can't help ya :P

For instance, GDP increase year over year is a good thing. Debt increase is a bad thing.

Hummm. It seems like, I am asking for to much. I looks like I want you all to do the job. I am sorry. I'm just looking for clarity. For example:

If I create a heat map for: Albania ONLY. What is our suggestions base on the information below?
Albania’s consumer confidence index decreased to -13.6 in the last quarter of 2019 from -12.3 in the previous three-month period. It was the lowest reading in consumer morale since the first quarter of 2015, as expectations over the next 12 months worsened regarding financial situation (6.1 from 7.8 in Q3)... Consumer Confidence in Albania averaged -12.27 points from 2003 until 2019, reaching an all time high of -6.30 points in the fourth quarter of 2011 and a record low of -19 points in the fourth quarter of 2004.

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

 

[svendiamond]

Create the heat map based on the column/field. In this case you're looking at one field which is Consumer Confidence Index. Based on a quick google search (Consumer confidence index - Wikipedia) it looks like a positive number is good. So in this case, green for the higher number, red for the lower/negative number...

 

[Corried]

Create the heat map based on the column/field. In this case you're looking at one field which is Consumer Confidence Index. Based on a quick google search (Consumer confidence index - Wikipedia) it looks like a positive number is good. So in this case, green for the higher number, red for the lower/negative number...

Ok. will do some more research...

 

[Corried]

Hummm. It seems like, I am asking for to much. I looks like I want you all to do the job. I am sorry. I'm just looking for clarity. For example:

If I create a heat map for: Albania ONLY. What is our suggestions base on the information below?
Albania’s consumer confidence index decreased to -13.6 in the last quarter of 2019 from -12.3 in the previous three-month period. It was the lowest reading in consumer morale since the first quarter of 2015, as expectations over the next 12 months worsened regarding financial situation (6.1 from 7.8 in Q3)... Consumer Confidence in Albania averaged -12.27 points from 2003 until 2019, reaching an all time high of -6.30 points in the fourth quarter of 2011 and a record low of -19 points in the fourth quarter of 2004.

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

Base on these values above should conditional formatting be place like the example below?...

 

[svendiamond]

You should just use the default settings... not try to enter your own values. Also, you see in that menu the "Type" has a drop-down menu and you've got the Minimum and Maximum set to Number, but you've got the Midpoint set to a Percent, but you've typed in -12.27. Instead of all these arbitrary numbers, just use "Percentile" which I think is the default anyway. And like 85%, 50% for mid, and 15% for minimum. Also do some Googling, for god's sake. Are you going to ask every single question on this forum? Without using Google at all? No offense but you're never going to get this project done without Googling some basic things.