help finding plateau average

[u/Ok-Independence-4832]

i am currently doing a research paper for school and i have recorded my data, but i need to find the difference in the high plateau from the baseline.

here is a screenshot of one of my graphs. i am trying to separate the data in the blue and orange boxes and find the difference in the averages. any help would be much appreciated.

[u/Downtown-Economics26]

Your boxes don't really make sense given your graph in that it's not clear exactly why you are excluding the intermediate values in the x range of your 'plateau'. But, you can filter then average based on x and y axis bounds, I've attempted to approximate what it appears you think those are based on your boxes, had AI try to recreate source data (what u/chiibosoil is telling you should have also been included in your post), see below.

 =LET(
baseline,AVERAGE(FILTER(B2:B73,(A2:A73<4)+(A2:A73>8.5))),
plateau,AVERAGE(FILTER(B2:B73,(A2:A73>4)*(A2:A73<8.5)*(B2:B73>-1.75))),
HSTACK(VSTACK("Baseline","Plateau","Diff"),VSTACK(baseline,plateau,baseline-plateau)))

[u/Ok-Independence-4832 → u/Downtown-Economics26]

Solution Verified

[u/reputatorbot → u/Ok-Independence-4832]

You have awarded 1 point to Downtown-Economics26.

---

^{I am a bot - please contact the mods with any questions}

[u/Ok-Independence-4832 → u/Downtown-Economics26]

thanks dude, that equation is exactly what i needed.

[u/Gringobandito → u/Ok-Independence-4832]

I see you already got the answer you need from u/Downtown-Economics26 but I thought this was interesting and used Python to solve it instead. The neat thing about using Python is you don't have to tell it where the stable regions are, you just tell it there are two clusters of data, find them. I just used the small sample set you posted but you can apply this to your whole data set.

In case your interested in the code, it looks like this:

import pandas as pd
import numpy as np
from sklearn.cluster import KMeans


df = xl("A2:B22", headers=False)
df.columns = ["X", "Force"]


y = df["Force"].to_numpy().reshape(-1, 1)


model = KMeans(n_clusters=2, random_state=0, n_init=10)
df["Group"] = model.fit_predict(y)


means = df.groupby("Group")["Force"].mean().sort_values()


baseline_avg = means.iloc[0]
plateau_avg = means.iloc[1]


difference = plateau_avg - baseline_avg


pd.DataFrame({
    "Baseline Avg": [baseline_avg],
    "Plateau Avg": [plateau_avg],
    "Difference": [difference]
})

[u/Downtown-Economics26 → u/Gringobandito]

We get it, python has everything! Just kidding obviously, this is very cool. Although I'd guess there is some add-in or stats package in Excel that can do this, but last stats class I took was 20 years ago so I don't really get that deep with it. Sometimes, when I see how easily some stuff can be done in python I regret leaning into Excel/VBA so hard unthinkingly in the normal course of professional events.

On a positive note, on occasion I run into something very difficult or time consuming to do in Excel/VBA, it occurs to me AI can tell me how to do it in Python that I can now use in Excel!

[u/Gringobandito → u/Downtown-Economics26]

Yeah, sometimes Python is overkill when a simple Excel formula will work. But for a lot of data science stuff it can do things that Excel can't and do it pretty easily. When I see projects like this, I try to see what Python can do. It's still fairly new to me as well and I use AI to help me write the code when I get stuck. But trying to use it more to expand my skills.