Problem with IF function

Hello,

it's not quite clear if the range with the values will grow over time and what aspect of that table you want to evaluate. 

If the table will eventually have more than one row of data, you can use formulas to calculate the cold, warm and hot values for all the rows in the data entry table. For that, I suggest that you move the cells from A9:B11 to G4:H6 or similar. Next, turn the data entry range into an Excel Table object: Select cell A4 and click Insert > Table. 

Now you can use structured references that always cover all table rows in your formulas. 

In the screenshot below, the formulas in cells H4 to H6 are as follows

When more data is added to the table, the cells with the formulas will take that new data into account, since it references the table columns by name, not cell ranges by rows.