Looking for a non-array, non-VBA function

[schmidthead]

Full disclosure: I'm a finance guy in Oil & Gas and I use Excel for modeling purposes. Basically I'm looking for a non-array formula and non-VBA UDF formula that can accomplish the below:

[TABLE="width: 270"]
<tbody>[TR]
[TD]Month[/TD]
[TD]Wells[/TD]
[TD]Production[/TD]
[TD]Total Production[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]16,500[/TD]
[TD="align: right"]181,500[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]11,939[/TD]
[TD="align: right"]119,391[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]9,529[/TD]
[TD="align: right"]95,290[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]8,013[/TD]
[TD="align: right"]72,121[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]6,962[/TD]
[TD="align: right"]62,655[/TD]
[/TR]
</tbody>[/TABLE]

I need a formula that will take the most recently drilled wells (11 in Month 5) and multiply it by the initial production of 16,500. The second most recently drilled wells (10 in Month 4) would be multiplied by 11,939, and so on. I can calculate the lumpsum production using SUMPRODUCT, OFFSET and ROW, but I really need the monthly production instead of the lumpsum. I have a nice UDF that works but my employer frowns upon VBA. I'd prefer not to use arrays either. Is there anyone on here that can help? I will buy you a steak dinner in Houston if you can solve this. I'm posting the VBA below if that helps.

Function SumRevProduct(R1 As Range, R2 As Range) As Variant
Dim i As Integer
If R1.Cells.Count <> R2.Cells.Count Then GoTo ErrHandler
If R1.Rows.Count > 1 And R1.Columns.Count > 1 Then GoTo ErrHandler
If R2.Rows.Count > 1 And R2.Columns.Count > 1 Then GoTo ErrHandler

For i = 1 To R1.Cells.Count
SumRevProduct = SumRevProduct + _
R1.Cells(IIf(R1.Rows.Count = 1, 1, i), _
IIf(R1.Rows.Count = 1, i, 1)) * _
R2.Cells(IIf(R2.Rows.Count = 1, 1, R2.Cells.Count + 1 - i), _
IIf(R2.Rows.Count = 1, R2.Cells.Count + 1 - i, 1))
Next i
Exit Function
ErrHandler:
SumRevProduct = "Input Error"
End Function

 

[James006]

Hello,

In cell D2, you can test following formula:

=C2*INDIRECT("B"&MATCH(9.99999E+307,$B$1:$B$20,1)+2-ROW())

Hope this will help

 

[Rick Rothstein]

Here is another formula for you to consider (place it in cell D2 and copy down)...

=INDEX(B$2:B$6,6-ROWS(D$1:D1))*C2

Note: The red highlighted number should equal the last row number containing data.

 

[schmidthead]

Actually it looks like I misspoke on what I needed. The scenario in question is more like:
Month Wells Production Total Production
1 9 16500 148500
2 9 11939 255951
3 10 9529 358212
4 10 8013 442268
5 11 6962 530955

Assume this info is located in A1:C5. The first Total Production calculation is C1*B1, the second calculation is C2*B1+C1*B2, third: C3*B1+C2*B2+C1*B3, fourth: C4*B1+C3*B2+C2*B3+C1*B4, fifth: C1*B5+C2*B4+C3*B3+C4*B2+C5*B1.

I can accomplish this with a user-defined function but would vastly prefer not to. Does anyone have any ideas?

 

[Rick Rothstein]

Put this formula in cell D2 and copy it down...

=B2*C2+IF(ROW(D1)>1,D1,0)

Note: Your listed values in D4 on down are incorrect.

 

[schmidthead]

Rick:

Thanks for the idea but I don't see that my calculations are off.

 

[Eric W]

Try putting this in D1 from your example of post 4, and copy down:

=SUMPRODUCT($B$1:$B1,SUBTOTAL(9,OFFSET($C$1,ROWS($C$1:$C1)-ROW($C$1:$C1),0)))

However, technically speaking it's an array formula, although you don't need to press Control+Shift+Enter.

 

[Tetra201]

... Assume this info is located in A1:C5. The first Total Production calculation is C1*B1...

Here is my take on it:

=SUMPRODUCT($B$1:B1,N(OFFSET($C$1:C1,ROWS($C$1:C1)-ROW($C$1:C1)+ROW($C$1)-1,)))

This formula is not sensitive to moving/inserting rows.

 

[Wanderer_geologist]

SChmidthead

The process is fairly basic and if the vba use is a no go for your boss, why don't you try to migrate the processing of those through powerbi (i'm simply suggesting an alternative and there is a lot of hype in finance to use that tool (i'm also in O&G, sitting close to my company finance department) ?

The formula and output are straighftorward in it
Format as an xls table your data input
Create a powerbi project, get data from your xls
create a Measure column to compute on a monthly basis the production x well number

The summation, trend etc, will be process through the reporting in power bi.

 

[schmidthead]

I'm unfamiliar with PowerBI. Is this an Excel add-in or third-party?