Hello,
I've created a spreadsheet in which I want to place a formula in cells F3-F8 that compares the HR's (E3-E8) to the HR Ranges (I3-I8) and assigns the correct Training Zone. The current values in F3-F8 are correct, but manually placed. Is this possible and if so, how? - any help would be much appreciated.
[Edited by Moderator]
MacBook Air 13″, macOS 15.5
This is easy to do - you just need to reformat your data a little.
While not strictly necessary, it would be advantageous to first move the HR Ranges and Training Zone into its own, separate table. That way it's separate from the individual sessions.
You might also want to put the column headers in the first row rather than in the second row as they are now (that's kind of what header rows are intended for).
Secondly, reformat the HR Range to have only the upper value (e.g '125') rather than the formatted range (e.g. '108-125'). The reason to do this is that XLOOKUP can automatically return the next-highest value in the range, so searching for, say, 120, will automatically return the match for 125 as the next highest, which is something it cannot do when the lookup range says '108-125'.
So now your HR table should look like:
Now you can set F2 to a simple XLOOKUP:
=XLOOKUP(E2,HR Range,Training Zone,"",1)
The 1 at the end equate to the options for 'exact or next largest' in the search, and
This is easy to do - you just need to reformat your data a little.
While not strictly necessary, it would be advantageous to first move the HR Ranges and Training Zone into its own, separate table. That way it's separate from the individual sessions.
You might also want to put the column headers in the first row rather than in the second row as they are now (that's kind of what header rows are intended for).
Secondly, reformat the HR Range to have only the upper value (e.g '125') rather than the formatted range (e.g. '108-125'). The reason to do this is that XLOOKUP can automatically return the next-highest value in the range, so searching for, say, 120, will automatically return the match for 125 as the next highest, which is something it cannot do when the lookup range says '108-125'.
So now your HR table should look like:
Now you can set F2 to a simple XLOOKUP:
=XLOOKUP(E2,HR Range,Training Zone,"",1)
The 1 at the end equate to the options for 'exact or next largest' in the search, and
Thank you so much, I’ll give that a go in the morning and let you know the outcome.
It worked perfectly. Thank you :)
I've rearranged some of the headers and input live data (copied from another sheet) into the cells.
[Edited by Moderator]
Formula for Comparing Data
