simple excel problem

parko202

Hey,

Not sure if this is the right spot but am having difficulty with excel, the data as shown here:

Plot by eoin.mcpartland, on Flickr

is info I am using for a final year project, I need the Depth value in column A to take the relevant value from column D by interpolating the associated Depth in column C.

I need the information to be able to interpolate the Depth and arrive at a corresponding Strength value.

Any help would be greatly appreciated!

mark renton

what does interpolate mean??

Joneser

Well first you will need to say in which way the values in columns A and D are to interact in order to return the strength value. Do you have a formula as otherwise this appears to be a maths problem instead of an excel problem.

mark renton

Joneser wrote: »

Well first you will need to say in which way the values in columns A and D are to interact in order to return the strength value. Do you have a formula as otherwise this appears to be a maths problem instead of an excel problem.

A and D are interpolating

parko202

Cheers for the reponses.

There is not really a relationship in the conventional sense as the strength profile in D is for a soil not an isotropic material so basically what I need excel to do is:

Strength Value of A12 (2.360758)

= (D4-D3)/(C4-C3)*(A12)

But to know to do that automatically,ensuring that it picks the correct ranges, is this possible? It can't really be done manually as there is a huge amount of data to process.

croo

it's still not 100% clear to me but I've had "few" glasses of wine!

What I understand is that; I interpolate A with C and then I need to find... a the closest value in Column D? or a range of cells in column D? And if this is so, do you need the row number from column D or the closest value in D I found?

The only searching I've used in excel is the vlookup function... failing standard functions, it is possible to easily create new functions in VBA to add a new function to help.

Could you perhaps extend your last example to explain WHICH value from Column D you expect returned?

mark renton

D3 and C3 are zero dude - why are you subtracting these?

this is same as D4/C4 * A12

Seth Brundle

Is the formula always to look at
= (D4-D3)/(C4-C3)*(Ax)

so that the strength of A13
= (D4-D3)/(C4-C3)*(A13)

and A14 is
= (D4-D3)/(C4-C3)*(A14)

Then use absolute references:
= ($D$4-$D$3)/($C$4-$C$3)*(A12)


(but I think I may have misunderstood you)

parko202

kbannon wrote: »

Is the formula always to look at
= (D4-D3)/(C4-C3)*(Ax)

so that the strength of A13
= (D4-D3)/(C4-C3)*(A13)

and A14 is
= (D4-D3)/(C4-C3)*(A14)

Then use absolute references:
= ($D$4-$D$3)/($C$4-$C$3)*(A12)


(but I think I may have misunderstood you)

That is right, until the penetration in column A goes over 100mm then, it needs to take a value between 100 (C4) and 105 (C5) in column C and then when it exceeds 105 it needs to move to a range from 105 (C5) to 110 (C6).

I apologise for how badly I am explaining this, trying to explain it to others has proved just as difficult!

Ill try again!

Columns A and C are both penetrations but they are at different increments, C is a standard test and so the strength is measured at a specific increment of penetration. A is significantly more variable and so values are not equally spaced.

I want the value of A to interpolate a value of strength from the strength profile in column D.

Any better?!

Really appreciate the help on this!

Seth Brundle

parko202 wrote: »

That is right, until the penetration in column A goes over 100mm then, it needs to take a value between 100 (C4) and 105 (C5) in column C and then when it exceeds 105 it needs to move to a range from 105 (C5) to 110 (C6).

Ok
How will Excel know if the penetration is over 100mm or over 105 etc?

Have you data in another sheet which will be say 108 and then a formula uses this in a lookup against this table (e.g. 108 on my other sheet is between 105 and 110 so therefore use A6 here in the calcs).

It reminds me of something smiliar when we had to know the volume of an irregular sized container based on strapping data.

croo

yeah vlookup can find the value in an array .. if its last parameter is TRUE then an exact match or the largest value less than the search value will be returned.

so,a cell with vlookup(A4,C3: D9999,1,TRUE) would return C3 ... so ZERO
while vlookup(A4,C3: D9999,2,TRUE) would return D3... also ZERO

All good so far, but I see a couple of problems.
1) we know the values but we don't know the values returned were from cells C3 & D3 so how can you select C4 & D4 to apply in your forumla!>
2) I rarely use excel but just recently I was using vlookup a lot to match up data from two worksheets that had approx 40k rows and it was very slow. I got the feeling your dataset would be considerably larger than that!?

I think 1 can be solved using the MATCH function but I've not used it and for 2 you just need a better machine that I had! that or switch to a database and programming language.

** note thespace between : & D to stop it turning into a smiley

FSL

You could do it with a macro which looped down column A and for each row containing a value looped down column C until it found a value greater than C(n) AND less than C(n+1) you could then set column E to your answer.

I am assuming that the data for columns C and D are a fixed number of values and that the data in column A has a variable number of values.