Hello,
I am working on a formula that has many nested IF statements. I am calculating distances from stores, where the distances are in a matrix. Here is a short version of the formula to get from store to store.
=INDEX(C3:H8,MATCH(A13,A3:A8,0),MATCH(B13,C1:H1,0))+IF(ISBLANK(B14),(INDEX(C3:H8,MATCH(B13,A3:A8,0),MATCH(C13,C1:H1,0))),INDEX(C3:H8,MATCH(B13,A3:A8,0),MATCH(B14,C1:H1,0)))+IF(ISBLANK(B15),(INDEX(C3:H8,MATCH(B14,A3:A8,0),MATCH(C14,C1:H1,0))),INDEX(C3:H8,MATCH(B14,A3:A8,0),MATCH(B15,C1:H1,0)))+IF(ISBLANK(B16),(INDEX(C3:H8,MATCH(B15,A3:A8,0),MATCH(C15,C1:H1,0))),INDEX(C3:H8,MATCH(B15,A3:A8,0),MATCH(B16,C1:H1,0)))
The index is the matrix. The problem I am having is that I want the formula to stop calculating and ignore the remaining parts after the IF statement is true (true statement = cell below is empty). I am currently getting an error when one of the stores is missing and is not the final store in the formula.
I have posted the matrix and store layout below. Please let me know if this possible, or if there is a better way to complete this!
Much apprecaited!
Sam
<TBODY>
</TBODY>
I am working on a formula that has many nested IF statements. I am calculating distances from stores, where the distances are in a matrix. Here is a short version of the formula to get from store to store.
=INDEX(C3:H8,MATCH(A13,A3:A8,0),MATCH(B13,C1:H1,0))+IF(ISBLANK(B14),(INDEX(C3:H8,MATCH(B13,A3:A8,0),MATCH(C13,C1:H1,0))),INDEX(C3:H8,MATCH(B13,A3:A8,0),MATCH(B14,C1:H1,0)))+IF(ISBLANK(B15),(INDEX(C3:H8,MATCH(B14,A3:A8,0),MATCH(C14,C1:H1,0))),INDEX(C3:H8,MATCH(B14,A3:A8,0),MATCH(B15,C1:H1,0)))+IF(ISBLANK(B16),(INDEX(C3:H8,MATCH(B15,A3:A8,0),MATCH(C15,C1:H1,0))),INDEX(C3:H8,MATCH(B15,A3:A8,0),MATCH(B16,C1:H1,0)))
The index is the matrix. The problem I am having is that I want the formula to stop calculating and ignore the remaining parts after the IF statement is true (true statement = cell below is empty). I am currently getting an error when one of the stores is missing and is not the final store in the formula.
I have posted the matrix and store layout below. Please let me know if this possible, or if there is a better way to complete this!
Much apprecaited!
Sam
Store # | 2002 | 2004 | 2005 | 2006 | 2007 | 2008 | |
Postal Code | L6V 1B7 | 1747 countryside | L6Z 1Y4 | L6T 4G8 | L7E 4Z8 | L7G 4B1 | |
2002 | L6V 1B7 | 0.00 | 12.40 | 7.56 | 6.74 | 25.40 | 18.35 |
2004 | 1747 countryside | 12.40 | 0.00 | 6.06 | 9.45 | 16.60 | 20.23 |
2005 | L6Z 1Y4 | 8.08 | 6.60 | 0.00 | 10.76 | 21.33 | 16.34 |
2006 | L6T 4G8 | 6.75 | 9.45 | 10.21 | 0.00 | 23.72 | 22.19 |
2007 | L7E 4Z8 | 25.39 | 16.59 | 20.76 | 23.23 | 0.00 | 32.23 |
2008 | L7G 4B1 | 18.36 | 20.23 | 16.53 | 22.16 | 32.26 | 0.00 |
Start | Store | End | |||||
2002 | 2004 | 52.93 | |||||
2005 | |||||||
2006 | 2007 | ||||||
**the highlighted cell contains the formula above. | |||||||
<TBODY>
</TBODY>