Calculate Average Cost over Distance in 1K Buckets

[spnclr]

Hello,

I am currently facing a challenge and hope someone can assist me. I have a table that logs the activity of wells, including their phase, the footage drilled each day, and the daily costs. We aim to evaluate how costs change over the distance drilled per well, categorized into buckets of 1,000 miles (e.g., 0-1,000, 1,000-2,000, etc.). The total footage drilled per well varies, with some wells having drilled 10,000 miles or more, while others have less. We are certain that average costs differ across these footage buckets, but we need to determine the extent of these differences.

Attached is a sample of fictional data and the workflow I have started.

Any help would be greatly appreciated!

[Yoshiro_Fujimori · Accepted solution]

Hi @spnclr ,

I am not exactly sure how you want the "Avg Cost Per Foot" calculated, but

I trid to categorize each row into bucktes of footage by each 1000 as below:

Here I assigned each row to one bucket for simplicity.

But if you need to split the cost proportionally to multiple buckets, it would take further consideration.

I hope this helps.

Workflow

Running Total

Group = {"wellname", "PHase Name"}

Create Running Total = {"Footage Made"}

Formula #1

[Footage] = 

ToString((CEIL([RunTot_Footage Made] / 1000) - 1) * 1000) +

" - " +

ToString((CEIL([RunTot_Footage Made] / 1000)) * 1000)

Formula #2

[Avg Cost Per Foot] = Round([Sum_Daily Cost] / [Sum_Footage Made],1)

[Total Cost] = [Avg Cost Per Foot] * 1000

Output

wellname	PHase Name	Footage	Sum_Daily Cost	Sum_Footage Made	Avg Cost Per Foot	Total Cost
Well 1	Phase 1	0 - 1000	50080.02999	261	192	192000
Well 1	Phase 1	1000 - 2000	146852.8874	1034	142	142000
Well 1	Phase 1	2000 - 3000	235235.6155	1420	166	166000
Well 1	Phase 1	3000 - 4000	552956.7248	983	563	563000
Well 1	Phase 1	4000 - 5000	44392.30559	844	53	53000

[Qiu]

@spnclr 
Though I am not quite able to understand your intention here, but somehow I feel you want to arrange the daily drill footage to a bucket of 5280 feed (1 mile) more or less.
If so, then it will be a Bin Packing problem, which is discussed briefly here.

https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/HELP-How-to-use-ITERATIVE-MACR...

I made some modification to the flow there and it is given the results as below.

[spnclr]

There are some techniques in this solution that I'm sure I'll be able to use in the future.

Thank you very much for taking a look!

[spnclr]

I think this solution will work for what we need.

Thank you so much for your help!

[Qiu]

Glad to be any help.