Use the previous non-zero value in a column in calculation within nested if.

[MisterGB]

Hey again!

In column R, I have the formula:

=MIN(ALS(OF($P3="Auto 2H sword";$P3="Auto Khopesh");$R2+3;ALS(OF($P3="Dismember";$P3="Cleave";$P3="Decimate";$P3="Sever";$P3="Gfury";$P3="Havoc";
$P3="Smash";$P3="Slice";$P3="Tuska";$P3="Sacrifice");$R2+8;ALS(OF($P3="Assault";$P3="slaughter";$P3="Destroy";$P3="Gflurry";$P3="Hurricane";
$P3="Quake";$P3="Btendrils");$R2-15;ALS(OF($P3="Beserk");$R2-100;ALS($P3="vBeserk";$R2-90;ALS($O3="Apot";$R2+25;ALS($O3="EnhApot";$R2+30)))))));100)

Yes, I know it's probably a trainwreck and it's also in Dutch. ALS(OF = IF(OR.

The table that this applies to has zero values in Column R that need to be ignored. The next non zero value above a given cell in column R needs to be used instead of $R3.

Currently column R has the following:

R3: 3
R4: 11 (as Cleave is in cell P4)
R5: 0
R6: 0
R7: 8 (as Decimate is in cell P7)

R7 needs to return to the 11 and add the 8 as in the formula.

As an added bonus, I have entered the formula into R3 and used addressing to fill column R.

I hope that makes some sense!

Thanks,

GB

 

[jasonb75]

I think that this is what you're asking for, formula is in english though, just needs converting back.

Book5
	P	Q	R
2
3	Auto 2H Sword		3
4	Cleave		11
5			11
6			11
7	Decimate		19
Sheet5

Cell Formulas
Range	Formula
R3:R7	R3	=MIN(IFERROR(LOOKUP(1E+100,1/(1/R$2:R2)),0)+IF(OR($P3="Auto 2H sword",$P3="Auto Khopesh"),3, IF(OR($P3="Dismember",$P3="Cleave",$P3="Decimate",$P3="Sever",$P3="Gfury",$P3="Havoc",$P3="Smash",$P3="Slice",$P3="Tuska",$P3="Sacrifice"),8, IF(OR($P3="Assault",$P3="slaughter",$P3="Destroy",$P3="Gflurry",$P3="Hurricane",$P3="Quake",$P3="Btendrils"),-15, IF(OR($P3="Beserk"),-100, IF($P3="vBeserk",-90, IF($O3="Apot",25, IF($O3="EnhApot",30))))))),100)

 

[MisterGB]

Thanks so much! I was able to combine the 3 repeating cells as each value returned correctly to smarten up the look.

Also, an infinitely eleganter solution I might add

 

[jasonb75]

an infinitely eleganter solution

That would be a lookup table

 

[jasonb75]

This is what I was referring to above, the list on the right can be hidden away in another sheet, this is one of the easiest ways to clean up long formulas.

Book5
	P	Q	R	S	T	U	V
2
3	Auto 2H Sword		3			Auto 2H Sword	3
4	Cleave		11			Auto Khopesh	3
5			11			Dismember	8
6			11			Cleave	8
7	Decimate		19			Decimate	8
8						Sever	8
9						Gfury	8
10						Havoc	8
11						Smash	8
12						Slice	8
13						Tuska	8
14						Sacrice	8
15						Assault	-15
16						slaughter	-15
17						Destroy	-15
18						Gflurry	-15
19						Hurricane	-15
20						Quake	-15
21						Btendrils	-15
22						Beserk	-100
23						vBeserk	-90
24
25						Apot	25
26						EnhApot	30
Sheet5

Cell Formulas
Range	Formula
R3:R7	R3	=MIN(IFERROR(LOOKUP(1E+100,1/(1/R$2:R2)),0)+IFERROR(VLOOKUP($P3,$U$3:$V$23,2,0),IFERROR(VLOOKUP($O3,$U$25:$V$26,2,0),0)),100)