Calculate Values Until a Condition is Met

[liquidlightning]

Hey all,

I have a spreadsheet with landmarks for an upcoming long run listed, and I'd like to have Excel calculate the mileage until the next available water source. I've provided a simplified version of the spreadsheet below. I'm comfortable using basics commands, like IF, AND, COUNT, etc., but upon browsing some threads I was quickly in over my head. The LOOKUP and INDEX commands legitimately intimidate me.

Essentially I want Column D to calculate miles until the next "Y" in Column C. It's easy enough to do myself on a small enough range, but where's the satisfaction in that?

Thanks!

 

[liquidlightning]

I should mention that I did try to sort it out myself before realizing this may be trickier than anticipated. For D2 I tried =IF(C3="Y",B3-B2,... but then I got lost. I don't want to just endlessly build =IF commands until the next instance of "Y" in Column C.

 

[StephenCrump]

Welcome to the Forum!

	A	B	C
1	Miles	Water	Miles to water
2	0		3
3	1		2
4	2		1
5	3	Y	0
6	4		5
7	5		4
8	6		3
9	7		2
10	8		1
11	9	Y	0
12	10		5
13	11		4
14	12		3
15	13		2
16	14		1
17	15	Y	0
18	16		-
Sheet1

Cell Formulas
Range	Formula
C2:C18	C2	=IFERROR(INDEX(A2:A$18,MATCH("Y",B2:B$18,))-A2,"-")

If the mileposts are always sequential 0, 1, 2, 3, 4 ... etc, you could simplify to
C2: =IFERROR(MATCH("Y",B2:B$18,)-1,"-")

 

[liquidlightning]

Thanks for the reply!

That helped for the majority, but I guess I'd prefer if the rows marked Y didn't return as 0 and would instead calculate miles until next water source. That way if I'm standing at a water source, I can check my printed table and know how long until I'll be at water again. Here's a screenshot of the actual spreadsheet if that adds context.

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C	D
1
2		0	Y	4.8
3		1.6	N	3.2
4		3.1	N	1.7
5		4.8	Y	4.7
6		8	F	1.5
7		8	-	1.5
8		9.5	Y	0.4
9		9.9	Y	6.8
10		15.1	F	1.6
11		16.7	Y	13.6
12		18.1	-	12.2
13		18.8	-	11.5
14		21.8	N	8.5
15		23.5	N	6.8
16		25.2	N	5.1
17		28.2	-	2.1
18		28.9	-	1.4
19		30.3	Y	6.8
20		31.9	F	5.2
21		37.1	Y	-
Main

Cell Formulas
Range	Formula
D2:D21	D2	=IFERROR(INDEX(B2:B$50,MATCH("Y",C3:C$50,)+1)-B2,"-")

 

[liquidlightning]

How about

Yes! That's it.

I tried adjusting the MATCH array to exclude the current row, but it wasn't coming out right. The +1 parameter fixed it. Perfect!

Now that I know which way is up I'll spend some time familiarizing myself with the arguments used here for future applications.

Thanks both!

 

[Fluff]

Glad we could help & thanks for the feedback.