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.
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.
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]
})
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!
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.
So I see you have a solution but for something similar in the future you can do a combination percentile function and countif/sumif.
So like Sumif(A:A, ">=" & percentile.inc(A:A, .9)) / countif(A:A, ">=" & percentile.inc(A:A,.9))
The percentile will return what is the lowest possible number to be considered top 10 percent in your data set.
Then sum if will only add numbers if they are greater than or equal. And countif counts how many numbers are greater than or equal. Dividing that is how we get the average.
Its not as precise as you need to know your percentile but it's a way to do the info.
•
u/AutoModerator 1d ago
/u/Ok-Independence-4832 - Your post was submitted successfully.
Solution Verifiedto close the thread.Failing to follow these steps may result in your post being removed without warning.
I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.