Formula to calculate the difference between two numbers based on a month in another column

Chevon Brownell

I am unsure if it is possible to do what I want, but I wanted to ask. I have attached a copy of my grid. I want a formula to calculate the difference between the largest and smallest numbers for each well for each month. For example, for well #2 in month one, find the largest number (58650300) and subtract the smallest number (58526600) to give me the total produced in the month. Then, repeat this for each well and each month. I need this formula to be used year after year, so I need it based on month and not date specific.

Weekly Well Readings.xlsx

Paul Newcome

Where will these formulas live? Are you able to provide a screenshot of a manually created example of what you are wanting to accomplish?

Chevon Brownell

I am unsure if the formulas can be incorporated into the sheet summary option in this sheet or if they need to be on a separate sheet. I am looking for any and all advice and help. I have yet to try to put a formula together. I only know what results I am looking for. I tried to explain it in my question.

Paul Newcome

 Are you able to provide a screenshot of a manually created example of what you are wanting to accomplish? "Every well for each month" could mean a couple of different things. Do you mean current month, or do you intend to have separate fields for Well A - Jan 2024, Well A - Feb 2024, Well B - Jan 2024, Well B - Feb 2024, etc.?

Chevon Brownell

Yes, I am trying to come up with a formula that will calculate the amount for each month for each well. My crew gets weekly readings and enters them into the grid, and I was looking for a way to quickly calculate how much it changes from the month's first reading to the last. Does that help explain it more?

Paul Newcome

Yes. Definitely. Next question would be… Will you have multiple years in the source data so that (for example) for January, we need to specify Jan 2024 vs Jan 2025? Or would you want current year? Or some other logic if necessary?

Chevon Brownell

I plan to clear out the grid each year, so the calculations should be based on month rather than year, if possible.

Paul Newcome

Ok. In that case you would use something along the lines of…

=MAX(COLLECT({Source Data Numbers Column}, {Source Data Well Column}, @cell = [Well #]@row, {Source Data Date Column}, IFERROR(MONTH(@cell), 0) = 1)) - MIN(COLLECT({Source Data Numbers Column}, {Source Data Well Column}, @cell = [Well #]@row, {Source Data Date Column}, IFERROR(MONTH(@cell), 0) = 1))

In the above, the 1s indicate January, so changing those to 2 will give you February, 3 for March, so on and so forth.

Chevon Brownell

I will try this. Should I put this on a separate sheet, in the sheet summary section, or on the same sheet as the well data?

Paul Newcome

I would suggest a separate sheet. The formula above is written out for a separate sheet that looks like your last screenshot.

Chevon Brownell

I am not getting your formula to work. I may not be using the correct Source Columns. Could you make your formula specific for the #2 well for January? Then, I can see exactly what columns to choose and change for each well and each month.

Paul Newcome

This is used as a column formula for January:

=MAX(COLLECT({Source Data Numbers Column}, {Source Data Well Column}, @cell = Well@row, {Source Data Date Column}, IFERROR(MONTH(@cell), 0) = 1)) - MIN(COLLECT({Source Data Numbers Column}, {Source Data Well Column}, @cell = Well@row, {Source Data Date Column}, IFERROR(MONTH(@cell), 0) = 1))

Chevon Brownell

I apologize for my confusion. Am I just copying and pasting exactly what is there or am I choosing columns from the weekly well sheet? When I tried to choose columns, it resulted in a zero. So I am doing something wrong.

Paul Newcome

You would copy/paste the formula, but then you have to create each {Cross Sheet Reference} to the source sheet and selecting the column indicated within the formula.

If you are still getting zero after double checking the cross sheet references, please provide a screenshot of the source data and the target sheet, and we can work through some troubleshooting.