Forum Discussion
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
| H4 | =COUNTIFS(Table1[Duration],">56") |
| H5 | =COUNTIFS(Table1[Duration],"<=56",Table1[Duration],">=10") |
| H6 | =COUNTIFS(Table1[Duration],"<10") |
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.
Hello Ingeborg
If your speculation is correct I would insert an additional column "Condition".
=LOOKUP(Table1[Duration],{0,10,56.0001},{"Hot","Warm","Cold"})And then I would insert a pivot table with "Condition" in row area and in values.
Yes, that approach is possible, but the Lookup function may be beyond the comfort zone of some people.
The beauty of Excel is that there is always half a dozen ways to do things, depending on where folks are in their particular learning journey.
Formulas are often the first step. Pivot tables will come a bit later. Undocumented functions like Lookup() are showing in the documentation as for compatibility to earlier versions, so that will freak people out.
I like to keep it nice and simple and build on that over time.