how to calculate time spent above a particular target?

Hello All,

I have created this chart (with the generous help of MrShorty and 6StringJazzer!)


I would like to calculate time spent above 100.

And I would like to eliminate the area below say... 40 (no data will ever appear there.)

Any help would be kindly appreciated!

Test chart.jpg

Shall be easy. Just draw a line (1) at 100. Then from every crossection of this line with your function draw the line down to the axis. Read all times and calculate differences (time5-time4)+(time3-time2).

This was mainly to focus your attention to yellow banner above the first post :-)

Thanks but I'm looking for a way to automate this...

Also... I'm not sure how posting some spreadsheet data would help explain what I'm looking for?

First reason: I'll not have to create dummy dataset myself.
Moreover you could write manually assessed/calculated result(s).
And this would be used to verify computed results.

PS. There is no point to use "Reply with Quote" as long as you refer to last message.
Everyone can see it just above your answer.

As for quoting: Have you ever read our forum rules? Available at: https://www.excelforum.com/forum-rul...rum-rules.html
Let me quote one of guidelines (listed below main rules):

2. Don't quote whole posts -- it's just clutter.*

Let me also comment on your behaviour: you act as you wouldn't look for help. Not willing to make your input - not expect work from others.

OK. I'm quiting to my cave. Thread unsubscribed.

@jw2000

Such rude and offensive behaviour towards members trying to help you will not be tolerated.

YOU are the one asking for help, and any help you get will be given for free and on a discretionary basis. Such ignorant outbursts are unlikely to endear you to helpers, so do not be surprised if the help dries up.

AliGW. quite right.

jw2000. You get volunteers helping you here for free. We are NOT your personal slaves to whom you can hand out verbal abuse.

And I would like to eliminate the area below say... 40 (no data will ever appear there.)

In the format axis dialog (same one we visited in a previous thread) for the vertical axis is a "Minimum" option. If you don't like Excel's automatic default choice here, simply uncheck the auto box and enter 40 (or whatever value makes sense for you at the time).

I think we need you to describe how you would like to calculate time above 100. Kaper's picture suggests and interpolation step followed by a simple sum of differences, but it isn't clear from your OP if you require the interpolation step.

The ‘offended individual’ is a senior member of the forum (guru status), which means that he is perfectly entitled to help police the forum. You received no cheeky responses: you were asked not to quote unnecessarily and to provide a sample workbook. If you continue to make rude comments about our any of our members, then you will be banned from here and therefore receive no further help here.

Do as you are asked and abide by our rules, please.

@AliGW

your inbox is full

I guess I was wondering if excel had a canned function that would do the interpolation.

Excel does not have a canned function. Are you required to use Excel? Gnumeric has a canned interpolation function that can do both linear and spline interpolations -- https://help.gnome.org/users/gnumeri...alysis.html.en

Edit to add: This might be a useful tutorial by Andy Pope: https://andypope.info/charts/intersection.htm Even though he uses two different "moving" series, your scenario is a case where one series moves and the other is constant at y=100 and you are looking for all the points where your series crosses the y=100 line. He uses one or more VBA UDF procedures to extract each intersection point -- assuming you are allowed to use VBA for this.

My INBOX is no longer full.

Please stop quoting whole posts.

sure thing.... karen

jw2000 has just earned a 7 day ban