Forum Discussion
help with coding via comparison operators!
Hello,
I've got a data set of about 1000 cells that I'm trying to have excel lump into categories. This is my coding sequence:
95+ - 3
I'm struggling to create a formula that incorporates all of these age categories into one formula. Even with one equation, right now I type:
=IF(64>H2<=74,1,0)
And it keeps assigning me a 0 even though the age in question is a 71 yo. Any way to easily categories my ages?
3 Replies
That's interesting how initial formula
=IF(64>H2<=74,1,0)
works.
First, it compares 64 with H2 and returns TRUE or FALSE, doesn't matter. After that it compares the result with 74. Logical TRUE or FALSE are both always less than any number, thus the result is always FALSE.
=IF(FALSE,1,0)
Finally we have always zero.
We may convert first logical into the number
=IF( --(64>H2)<=74,1,0)
which compares 0 or 1 with 74, result is always TRUE.
Finally we have always 1 in this case.
Very often Excel automatically converts logical TRUE/FALSE into the number in formulas, thus you don't need double dash. Not in such case.
Hi Philippe,
1. The IF formula you have tried should be like this.
=IF(AND(H2>64,H2<=74),1,0)
2. I can't get you what you want.
3. Show screet shot of data. And ask clearly what you want. COUNT or anything else.
Sample file or screenshot much better to solve.
Hello,
consider the following screenshot:
The age thresholds are listed in column A. Note that you only need the start of the age bracket. Then you can lookup the value with a VLookup formula that uses an approximate match. The formula in cell E3 is
=VLOOKUP(E2,$A$1:$B$5,2,TRUE)
An approximate match requires that the data is sorted ascending in column A. Then the formula will match the age in column A that is smaller than or equal to the age in E2 and return the value in the corresponding cell in column B.
