SOLVED

excel IF formula

Copper Contributor

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

 

 

3 Replies
best response confirmed by VI_Migration (Silver Contributor)
Solution

@Catherine800 

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.

S1437.png

The formula in J7 is

=($G7>=J$6)*($G7<K$6)*$G7

This can be filled to the right to N7, then down.

@Catherine800 

 

Perhaps something like this. 

 

JMB17_0-1652643053439.png

 

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.

 

Brilliant, it works perfect! thank you so much, Catherine
1 best response

Accepted Solutions
best response confirmed by VI_Migration (Silver Contributor)
Solution

@Catherine800 

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.

S1437.png

The formula in J7 is

=($G7>=J$6)*($G7<K$6)*$G7

This can be filled to the right to N7, then down.

View solution in original post