I've been racking my brain for a week, and I need some help
For a university experiment, we are collecting data on how much pressure subjects can exert on a force plate, up until the point of fatigue. Data will be captured in a CSV file and we want to do analysis/reporting in Excel.
Data collection is continuous, but due to individual differences, the duration and pressure patterns in a session will be highly variable.
All rows will need to be preserved for record-keeping, but low and no-effort rows will need to be excluded from calculations.
Attachment 399011
Columns of interest are:
- Milliseconds - in intervals of 500, to signify half-seconds
- Pressure - pressure exerted on the force plate
- Target - arbitrary pressure goal, set by administrator. May be changed mid-session
- Percentage - (Pressure/Target)*100
Where I need help
1. What formula would permit individual session start and end rows to be identified within a data file according to the criteria, where 0>PRESSURE<20 for duration of time => 2 minutes (This would be derived from a difference in MILLISECONDS, I get that--where I really need help is a formula to automatically parse the data, rather than requiring manual gap identification.
2. Within a valid session (As defined by the above criteria), how could I calculate something like MAX duration, where the MAX pressure is identified, and then we calculate how long that pressure was continuously sustained - I think this one gets glitchy, using MAX, since as I understand it MAX can only be a value that appears once in the data set. Seems like this might be something related to SUMIF, but I can't figure it out past that idea.
3. Ideally, these sessions would be graphed so the pressure can be visually compared to the target, over time. That one *should* be easy, but the aspiration was to use a macro for standard reporting. Can Macros parse data, calculate, and graph the results, even with a highly variable data set?
4. I don't know VB, so I was anticipating recording the Macro the very old fashioned way, of typing in cells, setting up charts, etc. Can it be done? If not, are there starter snippets that I can use, or what rudimentary sites do you recommend?
fwiw, I've fiddled with Pivot tables and outlining, but it doesn't seem applicable. You can view the datasheet to see where I'm at with the modeling.
THANK YOU!
Bookmarks