VBA to interpolate between depths

brandon20

Board Regular
Joined
Feb 12, 2014
Messages
203
Hi,

I have two worksheets. Both have columns for (well name) and (Depth), one worksheet has a column for (pressure) and the other has a column for (Surface) that is associated with the well name and depth.
Simple enough.
What I need is to match up the surface from worksheet 2, with its associated pressure in worksheet 1.
The issue is that the pressure in worksheet 1 will occur at a depth in a BETWEEN the surfaces in worksheet 2.
I need the shallower (less depth) surface to populate the cell of this pressure.

It is all easier to see in this example:

Worksheet 1:
I filled in two (Surfaces) that would be populated if I had the code
WELLNAME
DEPTH
PRESSURE
SURFACE
RA-0074
8953
4185.7998
RA-0074
9000
4220
RA-0074
9028
4245
RA-0074
9110
4292
RA-0074
9165
4244.7002
RA-0074
9166
4244.6001
RA-0074
9182
4220.3999
RA-0074
9220
4238.1001
Z51CH
RA-0074
9350
4270.5
Z46CH
RA-0074
9447
4312
RA-0074
9539
4352
RA-0074
9570
4372.7998
RA-0074
9641
4539

<tbody>
</tbody>

Worksheet 2:
Wellname
DEPTH
Surface
RA-0074
9202.7
Z52_1
RA-0074
9204.9
Z51CH
RA-0074
9232.9
Z51
RA-0074
9237.3
Z48CH
RA-0074
9250.93
Z48
RA-0074
9313.2
Z46CH
RA-0074
9415.6
Z46
RA-0074
9421.4
Z44CH
RA-0074
9512.7
Z44

<tbody>
</tbody>

As you can see, Z51CH in worksheet 2 occurs at Depth 9204.9. The next surface in worksheet 2 occurs at depth 9232.9.
In worksheet 1, I filled in Z51CH at Depth 9220. This is because the Z51CH surface from worksheet 2 occurred at a depth 9204.9 and the NEXT surface (Z51) occurred at a depth 9232.9.
Worksheet 2, Z51CH surface is a range from 9204.9 to 9232.9.
Worksheet 1, pressure at depth 9220 falls BETWEEN worksheet 2 surface range 9204.9 to 9232.9
Therefore I need that pressure to be called Z51CH.


Any help would be greatly appreciated….
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,110
Office Version
  1. 365
Platform
  1. Windows
I cant help with VBA but this formula will do it. It will need adapting. I put your first table in A1:D14 and your lookup table in A17:C26 of the same worksheet. There may be easier formulas! Put this in D2:

=INDEX($C$18:$C$26,MATCH(IFERROR(LARGE($B$18:$B$26,COUNTIF($B$18:$B$26,">"&B2)+1),MIN($B$18:$B$26)),$B$18:$B$26,0))
 

brandon20

Board Regular
Joined
Feb 12, 2014
Messages
203
Hi,

Thank you so much! An equation that could do this would be much better for me as I am no expert with VBA, I can learn from an equation to adapt it to different problems in the future.

I think this one almost has it.

I noticed one issue,

When I ran the equation on multiple wells it seemed to come up with incorrect answers.
In column A I have 1000 different well names, RA-0001, RA-0100HST, RA-0500...... bla bla bla.

When I ran the equation on a single well it worked great. What would I throw into this equation to make it go through by specific well name first? I noticed it was not looking in column A.

Thanks again!

Brandon
 

Watch MrExcel Video

Forum statistics

Threads
1,109,364
Messages
5,528,265
Members
409,811
Latest member
pjwhyman

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top