Forum Discussion
Solved
excel IF formula
Hello, I have a commercial dive school and am creating an excel spreadsheet to calculate dive minutes and allocate these minutes to specific columns which represent different dive depth.
This is the statement I am trying to create in excel.
If the divers depth is between 0-33 feet then the depth recorded in G7 cell is copied to j7. If the depth is between 33-49.5 ft then G7 is copied to k7. If the depth is between 49.5-66 ft then G7 is copied to l7. IF the depth is between 66-98 ft then G7 is copied to M7. And if the depth is to 99 ft then G7 is copied to N7. Otherwise "0" is entered in each J7, K7, L7, M7, N7.
So for every dive one of the cells (J7, K7, L7, M7, N7) will have the dive depth that was recorded on G7.
Much appreciated,
Catherine
I'd enter the thresholds 0, 33,49.5 etc. in a row above the cells where you want the results. In the screenshot below, they are in J6:N6. I also added a value larger than the deepest dive in O6.
The formula in J7 is
=($G7>=J$6)*($G7<K$6)*$G7This can be filled to the right to N7, then down.
Perhaps something like this.
Edit: The ∞ symbol can be input by holding down the alt key and hitting 236 on the number keypad. Excel treats any text value as greater than any number.
I'd enter the thresholds 0, 33,49.5 etc. in a row above the cells where you want the results. In the screenshot below, they are in J6:N6. I also added a value larger than the deepest dive in O6.
The formula in J7 is
=($G7>=J$6)*($G7<K$6)*$G7This can be filled to the right to N7, then down.
- Brilliant, it works perfect! thank you so much, Catherine
