VBA or excel help to update a range in an equation

[brandon20]

Hi,

I have this equation. It works great. But only for one well at at time. I need to make it work for multiple wells.
(you dont really need to know exactly how the equation works just look to the WHAT I NEED part)


=INDEX(Survey!$C$2:$C$5,MATCH(IFERROR(LARGE(Survey!$B$2:$B$5,COUNTIF(Survey!$B$2:$B$5,">"&B2)+1),MIN(Survey!$B$2:$B$5)),Survey!$B$2:$B$5,0))

The equation goes into the Survey! worksheet and looks at one wells range of values between rows 2:5 (see below in bold)
INDEX(Survey!$C$2:$C$5,MATCH(IFERROR(LARGE(Survey!$B$2:$B$5,COUNTIF(Survey!$B$2:$B$5,">"&B2)+1),MIN(Survey!$B$2:$B$5)),Survey!$B$2:$B$5,0))

If B2 (see below in bold) falls within the Survey! sheet range it places a value from Survey! into the cell where I am running the equation.
INDEX(Survey!$C$2:$C$5,MATCH(IFERROR(LARGE(Survey!$B$2:$B$5,COUNTIF(Survey!$B$2:$B$5,">"&B2)+1),MIN(Survey!$B$2:$B$5)),Survey!$B$2:$B$5,0))


WHAT I NEED:

I was wondering if there was a way in VBA or in excel to update the equation.
I need to update the Survey! sheet part of the equation to change the range when the B2 part of the equation reaches a different well. (I feel like there is an better way to say that but I cant think of one).
This example will clear it up.

Here are my two data sheets
(Survey Sheet)

Wellname	MD	X
RA-0001	0	964038.29
RA-0001	9253	988888.21
RA-0001	9256	988887.21
RA-0001	10337	964038.29
RA-0002	0	965555.35
RA-0002	9695	965555.35

<colgroup><col width="118" span="3" style="width:89pt"> </colgroup><tbody>
</tbody>

(Sheet where the equation is run in column C (starting in C2))

WELLNAME	TOP_DEPTH
RA-0001	9238
RA-0001	9254
RA-0002	7609
RA-0002	7630
RA-0002	7630

<colgroup><col span="2"></colgroup><tbody>
</tbody>

So as you see there are two wells here, RA-0001 and RA-0002. The equation above is only run for RA-0001 (ie. the range Survey!(C2:C5) which is the range of values for RA-0001)
What I need is the equation to change when I get to a new well like RA-0002. (ie. I need the range to change to Survey!(C6:C7) when the equation gets to cell C4 in my equation sheet.)

For example when my equation reaches RA-0002 starting in row 4 I would want it to change to look like this:
INDEX(Survey!$C$6:$C$7,MATCH(IFERROR(LARGE(Survey!$B$6:$B$7,COUNTIF(Survey!$B$6:$B$7,">"&B4)+1),MIN(Survey!$B$6:$B$7)),Survey!$B$6:$B$7,0))

Basically what I need is some way to change the range of data to match to specific wells. I have no clue how to go about this in VBA or in Excel.
I tried to implement a Vlookup into the equation but could not figure out how to get it to work, if it even could.



Thank you for any ideas
Brandon

 

[steve the fish]

Does this work?

In C2:=MIN(IF(Survey!$A$2:$A$7=A2,IF(Survey!$B$2:$B$7>B2,Survey!$C$2:$C$7)))

Entered CNTL SHIFT ENTER

If it doesn't provide a sample with the expected results.

 

[brandon20]

Hi,
Thanks for the reply.
I changed some of the values in the survey sheet to show the difference.

Your equation did not work exactly. The equation I have interpolates the values.
Where your equation gave 3 twice the equation I was using before finds 764038.29 and 9.
(In the survey sheet the first two values are 0 and 9253. Since 9238 in the equation sheet is less than 9253 my equation gives the value in C2 of the Survey sheet.)
(The next two values in the survey sheet are 9253 and 9256, since 9254 in the equation sheet is less than 9256 and also greater than 9253 in the Survey sheet. the calculation gives the value 9 in cell C3 of the equation sheet (the value from 9253 in the survey sheet).
So thats how the equation works. I had to change the equations range for well RA-0002. Which is what I am trying to figure out how to automate or add into the equation.

YOUR EQUATION
(Equation sheet (column C is the equation answer)

WELLNAME	TOP_DEPTH
RA-0001	9238	3
RA-0001	9254	3
RA-0002	7609	765555.35
RA-0002	7630	765555.35
RA-0002	7630	765555.35
RA-0002	7683	765555.35

<colgroup><col span="3"></colgroup><tbody>
</tbody>

(Survey Sheet)

Wellname	MD	X
RA-0001	0	764038.29
RA-0001	9253	9
RA-0001	9256	3
RA-0001	10337	764038.29
RA-0002	0	765555.35
RA-0002	9695	765555.35

<colgroup><col width="118" span="3" style="width:89pt"> </colgroup><tbody>
</tbody>

MY EQUATION
What I need the answer to be using the same survey sheet as above:
(Equation Sheet)

WELLNAME	TOP_DEPTH	Equation
RA-0001	9238	764038.29
RA-0001	9254	9
RA-0002	7609	765555.35
RA-0002	7630	765555.35
RA-0002	7630	765555.35
RA-0002	7683	765555.35

<colgroup><col span="3"></colgroup><tbody>
</tbody>

Here are the equations in this sheet:

WELLNAME	TOP_DEPTH	Equation
RA-0001	9238	INDEX(Survey!$C$2:$C$5,MATCH(IFERROR(LARGE(Survey!$B$2:$B$5,COUNTIF(Survey!$B$2:$B$5,">"&B2)+1),MIN(Survey!$B$2:$B$5)),Survey!$B$2:$B$5,0))
RA-0001	9254	INDEX(Survey!$C$2:$C$5,MATCH(IFERROR(LARGE(Survey!$B$2:$B$5,COUNTIF(Survey!$B$2:$B$5,">"&B3)+1),MIN(Survey!$B$2:$B$5)),Survey!$B$2:$B$5,0))
RA-0002	7609	INDEX(Survey!$C$6:$C$7,MATCH(IFERROR(LARGE(Survey!$B$6:$B$7,COUNTIF(Survey!$B$6:$B$7,">"&B4)+1),MIN(Survey!$B$6:$B$7)),Survey!$B$6:$B$7,0))
RA-0002	7630	INDEX(Survey!$C$6:$C$7,MATCH(IFERROR(LARGE(Survey!$B$6:$B$7,COUNTIF(Survey!$B$6:$B$7,">"&B5)+1),MIN(Survey!$B$6:$B$7)),Survey!$B$6:$B$7,0))
RA-0002	7630	INDEX(Survey!$C$6:$C$7,MATCH(IFERROR(LARGE(Survey!$B$6:$B$7,COUNTIF(Survey!$B$6:$B$7,">"&B6)+1),MIN(Survey!$B$6:$B$7)),Survey!$B$6:$B$7,0))
RA-0002	7683	INDEX(Survey!$C$6:$C$7,MATCH(IFERROR(LARGE(Survey!$B$6:$B$7,COUNTIF(Survey!$B$6:$B$7,">"&B7)+1),MIN(Survey!$B$6:$B$7)),Survey!$B$6:$B$7,0))

<colgroup><col span="2"><col></colgroup><tbody>
</tbody>

 

[steve the fish]

This formula is producing the same results as your sample. Test it and see if it is ok

=MIN(IF(Survey!$A$2:$A$7=A7,IF(Survey!$B$2:$B$7<B7,Survey!$C$2:$C$7)))

 

[steve the fish]

Sorry I keep forgetting that < symbols don't work unless theres a space between them

=MIN(IF(Survey!$A$2:$A$7=A2,IF(Survey!$B$2:$B$7 < B2,Survey!$C$2:$C$7)))

 

[brandon20]

Hi,

Sorry I didnt get back to you.

I ran the equation and it did work for my little example. I tried it on another example and it did not work.
I suppose the example was too simple...



Using your Equation (I pressed ctrl shift enter)
Equation sheet

Wellname	MD	Equation
RA-0062	7375	1
RA-0062	7376	1
RA-0062	7384	1
RA-0062	7384	1
RA-0062	7390	1
RA-0062	7413	1
RA-0062	7428	1
RA-0062	7430	1
RA-0062	7451	1

<colgroup><col><col span="2"></colgroup><tbody>
</tbody>

(survey sheet)

Wellname	MD	Surface
RA-0062	7281.73	1
RA-0062	7355.22	2
RA-0062	7359.37	3
RA-0062	7366.97	4
RA-0062	7368.74	5
RA-0062	7371.69	6
RA-0062	7371.69	7
RA-0062	7378.1	8
RA-0062	7384.44	9
RA-0062	7392.09	10

<colgroup><col width="146" span="3" style="width:110pt"> </colgroup><tbody>
</tbody>

Using my equation
Equation Sheet

Wellname	MD	Equation
RA-0062	7375	6
RA-0062	7376	6
RA-0062	7384	8
RA-0062	7384	8
RA-0062	7390	9
RA-0062	7413	12
RA-0062	7428	15
RA-0062	7430	15
RA-0062	7451	16

<colgroup><col><col span="2"></colgroup><tbody>
</tbody>

Same Survey sheet as above, (there are some more values I did not include.)




Im sure there is a much simpler equation than the one I have, but I know mine works (but only for one well at a time).
That is why I was trying to find out how to automate the change, well by well. Either by altering the equation (or adding an additional one) or by VBA.
Not sure how.


Thanks though,
Brandon

 

[steve the fish]

=MAX(IF(Survey!$A$2:$A$7=A2,IF(Survey!$B$2:$B$7 < B2,Survey!$C$2:$C$7)))

Not sure if im totally understanding what you are trying to do.

RA-0062	7371.69	6
RA-0062	7371.69	7

<colgroup><col width="71" style="width: 53pt; mso-width-source: userset; mso-width-alt: 2596;"><colgroup><col width="64" style="width: 48pt;" span="2"><tbody>
</tbody>

Why for instance is this true?

 

[brandon20]

Hi, I tried to simplify my example by adding numbers but it has backfired and become more confusing now.
The reason those are different values is because of the nature of the data I am working with.
I will give you the exact explanation of what I am trying to do.





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.

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 equation

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 associated with Z51CH.

I am just trying to interpolate the values.


This equation does this and works.

INDEX(Sheet2!$C$2:$C$10,MATCH(IFERROR(LARGE(Sheet2!$B$2:$B$10,COUNTIF(Sheet2!$B$2:$B$10,">"&B2)+1),MIN(Sheet2!$B$2:$B$10)),Sheet2!$B$2:$B$10,0))

<tbody>
</tbody>

But. once again, it only works for one well at a time. I need a way to make it work for multiple.
I could not see a way to do that with this equation unless I could figure out how to change the Sheet2 range to match the range of the next well on the fly.


Thank again, sorry to be confusing.

 

[steve the fish]

Im sure theres a less complex way but I think this works for you:

=INDEX(Sheet2!$C$2:$C$10,IFERROR(MATCH(1,IF(A2=Sheet2!$A$2:$A$10,IF(B2>Sheet2!$B$2:$B$10,1))),MATCH(MIN(IF(A2=Sheet2!$A$2:$A$10,Sheet2!$B$2:$B$10)),Sheet2!$B$2:$B$10,0)))

Enter CNTL-SHIFT-ENTER

 

[brandon20]

Amazing. Thank you so much. Sorry for being confusing in the beginning.

This works perfectly.

Thank you