Averages and vlookup solution?

**zbor** · 09-06-2010, 03:56 AM

Something like this:

**Markshr** · 09-06-2010, 04:05 AM

Thats great!

Thanks guys!

**Markshr** · 09-06-2010, 05:38 AM

I have tried to mimic the formula in cell R7 supplied by zbor, in R25

But can't seem to nail it

Anyone?

**Markshr** · 09-06-2010, 06:08 PM

I’m still trying to finish this formula.

Just can’t seem to get over the line,

Can anyone help?

**DonkeyOte** · 09-06-2010, 06:23 PM

Not entirely sure I follow but perhaps the INDEX element you're looking for is along the lines of:

INDEX($D$25:$N$31,MATCH($R$5,$C$25:$C$31),MATCH($R$23,$D$24:$F$24,0)+($R$21-1)*3)

**Markshr** · 09-06-2010, 06:44 PM

Thanks for the help.

That’s allmost it, but it doesn't seem to quite work with land descriptions "2" and "3"

For example if I have a land description of "2" in cell R21 on the attached,

and a soil permeability of "medium" IN R23 - it should return a vale of "0.32" from cell "i25"

Any ideas?

**DonkeyOte** · 09-06-2010, 06:54 PM

I did not notice that you have an inconsistent number of columns in each sub table... (1) uses 3 columns [D:F], (2) [G:J] and (3) [K:N] both use 4 columns and merged cells (best avoided)

Goes without saying that a consistent layout generally simplifies the calculations.

Let me know if you are going to modify the tables so that they are consistent (advised - and this means removed the merged cells) or you intend to leave as they are.