How to stop a formula when the IF statement is true

[sb1989]

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

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>

 

[GFV]

sb1989,
Have you considered to split the formula? Maybe you can write the Start # (2002) in cell B13, and the End# (2007) in cell B17 then the formula in row C14 would be =IF(B14<>"",INDEX($C$3:$H$8,MATCH(B13,$A$3:$A$8,0),MATCH(B14,$C$1:$H$1,0)),"") and you can copy this formula down covering as much cells as you want. The cell C12 can have formula =SUM(C13:C19) to add all individual distances. The good thing is that you can expand for as many rows as you need; just copy the formula to calculate the distance and make sure the addition =SUM(C13:C19) includes the new rows.
I hope this helps,
GFV