# Area Under a Curve Question **URL:** \ **Category:** Factual Questions\ **Created:** [January 14, 2015, 6:08pm UTC](https://boards.straightdope.com/t/area-under-a-curve-question/709835 "2015-01-14T18:08:23Z")\ **Posts on this page:** 17\ **Page:** 1
**Author:** ![dolphinboy](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/dolphinboy/32/330_2.png) [@dolphinboy](https://boards.straightdope.com/u/dolphinboy)\ **Post date:** [January 14, 2015, 6:08pm UTC](https://boards.straightdope.com/t/area-under-a-curve-question/709835/1 "2015-01-14T18:08:23Z")
This is something I should know from middle school, but with today’s tools I’m hoping it’s easier to do than it was 40 years ago. I create line graphs for work using Excel. I need to be able to bisect one of these graphs horizontally at a specific point and determine the area of the curve above and below the line. Is there some easy way to do this that doesn’t involve solving equations? If someone can provide me a simple example of how it’s done that might help me understand it better. ---
**Author:** ![Chronos](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/chronos/32/134_2.png) [@Chronos](https://boards.straightdope.com/u/Chronos)\ **Post date:** [January 14, 2015, 6:24pm UTC](https://boards.straightdope.com/t/area-under-a-curve-question/709835/2 "2015-01-14T18:24:11Z")
Important question: How are you drawing your lines? Are you just connecting the dots, or are you fitting a smooth curve to the data points? Also, if you are connecting the dots, do you consider the jagged bunch of line segments to be the “true” curve, or are you more interested in the “fundamental” curve of which that’s just an approximation? ---
**Author:** ![beowulff](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/beowulff/32/542_2.png) [@beowulff](https://boards.straightdope.com/u/beowulff)\ **Post date:** [January 14, 2015, 6:25pm UTC](https://boards.straightdope.com/t/area-under-a-curve-question/709835/3 "2015-01-14T18:25:47Z")
How do you go about generating the graphs? A simple approximation would be to take the rectangular height of each point on the curve (above and below the horizontal line), multiply by the width of each bin, and sum them. ---
**Author:** ![dolphinboy](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/dolphinboy/32/330_2.png) [@dolphinboy](https://boards.straightdope.com/u/dolphinboy)\ **Post date:** [January 14, 2015, 7:44pm UTC](https://boards.straightdope.com/t/area-under-a-curve-question/709835/4 "2015-01-14T19:44:47Z")
> [@Chronos](#): > > Important question: How are you drawing your lines? Are you just connecting the dots, or are you fitting a smooth curve to the data points? Also, if you are connecting the dots, do you consider the jagged bunch of line segments to be the “true” curve, or are you more interested in the “fundamental” curve of which that’s just an approximation? I am just connecting the dots and not fitting a smooth curve to the data points. I need to include all points under the “curve”, even the jagged bunch of line segments. If I plot the data for a year it will be mostly flat, but for a few weeks there will be large peaks. After those weeks it becomes mainly flat again at the same level as before the peaks happen. I want to draw a line at the level of the flat area so I can determine how much area is between the flat part and the top of the various peaks. ---
**Author:** ![Thudlow\_Boink](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/thudlow_boink/32/320_2.png) [@Thudlow\_Boink](https://boards.straightdope.com/u/Thudlow_Boink)\ **Post date:** [January 14, 2015, 7:50pm UTC](https://boards.straightdope.com/t/area-under-a-curve-question/709835/5 "2015-01-14T19:50:08Z")
By slicing the region vertically at the data points, you can carve it up into regions that are easy to calculate the area of (like a rectangle with a triangle on top), and add those individual areas together. ---
**Author:** ![dolphinboy](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/dolphinboy/32/330_2.png) [@dolphinboy](https://boards.straightdope.com/u/dolphinboy)\ **Post date:** [January 14, 2015, 8:06pm UTC](https://boards.straightdope.com/t/area-under-a-curve-question/709835/6 "2015-01-14T20:06:54Z")
> [@Thudlow\_Boink](#): > > By slicing the region vertically at the data points, you can carve it up into regions that are easy to calculate the area of (like a rectangle with a triangle on top), and add those individual areas together. Is there an application around than can do this easily? ---
**Author:** ![friedo](https://avatars.discourse-cdn.com/v4/letter/f/8edcca/32.png) [@friedo](https://boards.straightdope.com/u/friedo)\ **Post date:** [January 14, 2015, 8:22pm UTC](https://boards.straightdope.com/t/area-under-a-curve-question/709835/7 "2015-01-14T20:22:45Z")
> [@dolphinboy](#): > > Is there an application around than can do this easily? Yes, it’s the one you’re using. Given that you are creating these graphs using Excel, it should be easy to set up a formula to calculate the needed areas from the original data. But without some more details about the problem it will be difficult to show an example. ---
**Author:** ![Kyrie\_Eleison](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/kyrie_eleison/32/7682_2.png) [@Kyrie\_Eleison](https://boards.straightdope.com/u/Kyrie_Eleison)\ **Post date:** [January 14, 2015, 8:31pm UTC](https://boards.straightdope.com/t/area-under-a-curve-question/709835/8 "2015-01-14T20:31:42Z")
Look for something involving Simpson’s rule. That’s a numerical method that is more or less what **Thudlow Boink** was suggesting. The basic idea is to break the area under a curve into small segments, approximate their area as polygons, and sum them. Usually, the smaller the segments, the more accurate the estimate. There are curves for which this doesn’t work well, but from your description, it seems that your problem is one where it would be a reasonable approach. A quick googling turns up some [instructions](http://smallbusiness.chron.com/solve-simpsons-rule-excel-55174.html) for doing this in Excel. I’m too lazy to evaluate them though, so I won’t vouch for them, and more than likely, you’ll need to adapt them for your particular situation. ---
**Author:** ![dolphinboy](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/dolphinboy/32/330_2.png) [@dolphinboy](https://boards.straightdope.com/u/dolphinboy)\ **Post date:** [January 14, 2015, 8:34pm UTC](https://boards.straightdope.com/t/area-under-a-curve-question/709835/9 "2015-01-14T20:34:14Z")
> [@friedo](#): > > Yes, it’s the one you’re using. Given that you are creating these graphs using Excel, it should be easy to set up a formula to calculate the needed areas from the original data. But without some more details about the problem it will be difficult to show an example. Let me try one more time to explain it. I am tracking RMA data using a simple Excel spreadsheet. On the Y axis is the number of RMAs received and on the X axis is time (weeks), so for a one year long graph I have 52 points, each with varying heights depending on the number of RMAs received for that particular week. For most of the year the RMA volume is fairly steady, but for 2 or 3 months of the year we see a spike in RMAs. This spike is related to a particular problem we are tracking. So we have the baseline level of RMAs and an additional amount of RMAs due to this specific problem. I want to know the area under the peaks but above the baseline so I can determine how much additional RMA activity is the result of this particular problem. How can I get Excel to tell me that? ---
**Author:** ![Leaffan](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/leaffan/32/299_2.png) [@Leaffan](https://boards.straightdope.com/u/Leaffan)\ **Post date:** [January 14, 2015, 8:40pm UTC](https://boards.straightdope.com/t/area-under-a-curve-question/709835/10 "2015-01-14T20:40:18Z")
Can’t this information be gleaned from the raw data? Every place I’ve worked has coded RMAs with a “reason for return.” Don’t you do that? ---
**Author:** ![dolphinboy](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/dolphinboy/32/330_2.png) [@dolphinboy](https://boards.straightdope.com/u/dolphinboy)\ **Post date:** [January 14, 2015, 8:43pm UTC](https://boards.straightdope.com/t/area-under-a-curve-question/709835/11 "2015-01-14T20:43:39Z")
> [@Kyrie\_Eleison](#): > > Look for something involving Simpson’s rule. That’s a numerical method that is more or less what **Thudlow Boink** was suggesting. The basic idea is to break the area under a curve into small segments, approximate their area as polygons, and sum them. Usually, the smaller the segments, the more accurate the estimate. There are curves for which this doesn’t work well, but from your description, it seems that your problem is one where it would be a reasonable approach. > > A quick googling turns up some [instructions](http://smallbusiness.chron.com/solve-simpsons-rule-excel-55174.html) for doing this in Excel. I’m too lazy to evaluate them though, so I won’t vouch for them, and more than likely, you’ll need to adapt them for your particular situation. Thanks, I’ll give it a try. ---
**Author:** ![beowulff](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/beowulff/32/542_2.png) [@beowulff](https://boards.straightdope.com/u/beowulff)\ **Post date:** [January 14, 2015, 8:52pm UTC](https://boards.straightdope.com/t/area-under-a-curve-question/709835/12 "2015-01-14T20:52:10Z")
Don’t think of this as a graphing problem. This is simply the sum (each month RMA’s - baseline). ---
**Author:** ![Thudlow\_Boink](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/thudlow_boink/32/320_2.png) [@Thudlow\_Boink](https://boards.straightdope.com/u/Thudlow_Boink)\ **Post date:** [January 14, 2015, 9:07pm UTC](https://boards.straightdope.com/t/area-under-a-curve-question/709835/13 "2015-01-14T21:07:18Z")
> [@Kyrie\_Eleison](#): > > Look for something involving Simpson’s rule. That’s a numerical method that is more or less what **Thudlow Boink** was suggesting. Yeah, although I was actually thinking of the “trapezoidal rule.” For an actual curved curve, Simpson’s Rule is more accurate, but the OP was talking about a graph made up of connected straight line segments, if I understood him properly. ---
**Author:** ![Kyrie\_Eleison](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/kyrie_eleison/32/7682_2.png) [@Kyrie\_Eleison](https://boards.straightdope.com/u/Kyrie_Eleison)\ **Post date:** [January 14, 2015, 9:27pm UTC](https://boards.straightdope.com/t/area-under-a-curve-question/709835/14 "2015-01-14T21:27:20Z")
> [@beowulff](#): > > This is simply the sum (each month RMA’s - baseline). Given the additional detail offered in post #9, I too, am wondering if you’re not making this a more complicated problem than it really is. If you have a weekly baseline that represents the norm, and data representing what is really happening, why is it difficult to to compare them on a weekly basis? > [@Thudlow\_Boink](#): > > Simpson’s Rule is more accurate, but the OP was talking about a graph made up of connected straight line segments, if I understood him properly. I think you may have understood him better than I did. :o ---
**Author:** ![Pasta](https://avatars.discourse-cdn.com/v4/letter/p/ecccb3/32.png) [@Pasta](https://boards.straightdope.com/u/Pasta)\ **Post date:** [January 14, 2015, 9:33pm UTC](https://boards.straightdope.com/t/area-under-a-curve-question/709835/15 "2015-01-14T21:33:36Z")
> [@beowulff](#): > > Don’t think of this as a graphing problem. > This is simply the sum (each month RMA’s - baseline). Agreed. To add a concrete example, if the whole calendar is 19 weeks long and weeks 8 through 16 are the ones of interest, just take the baseline from the outside weeks (in the example below, avg = 48.2 per week under normal conditions) and subtract it off. That yields the third column here: ```auto columns: week, RMAs, RMAs-baseline 1 56 7.8 2 42 -6.2 3 46 -2.2 4 55 6.8 5 45 -3.2 6 46 -2.2 7 47 -1.2 8 86 37.8 9 89 40.8 10 76 27.8 11 87 38.8 12 89 40.8 13 92 43.8 14 90 41.8 15 87 38.8 16 65 16.8 17 54 5.8 18 45 -3.2 19 46 -2.2 . . . . . . . . . ``` Then just sum up (or average, as desired) the excess rates in the weeks of interest, #8 through #16. Here, the excess in weeks 8 through 16 comes to an average of 33.4 RMAs per week. You can choose to graph the data and overlay a line at 48.2 as well, but there’s no reason to use that graph as a calculation tool. ---
**Author:** ![Chronos](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/chronos/32/134_2.png) [@Chronos](https://boards.straightdope.com/u/Chronos)\ **Post date:** [January 14, 2015, 9:36pm UTC](https://boards.straightdope.com/t/area-under-a-curve-question/709835/16 "2015-01-14T21:36:30Z")
> [@](#): > > Let me try one more time to explain it. I am tracking RMA data using a simple Excel spreadsheet. On the Y axis is the number of RMAs received and on the X axis is time (weeks), so for a one year long graph I have 52 points, each with varying heights depending on the number of RMAs received for that particular week. Don’t think in terms of the X and Y axis of the graph. Think in terms of the columns of the spreadsheet that were used to create the graph. ---
**Author:** ![Punoqllads](https://avatars.discourse-cdn.com/v4/letter/p/d2c977/32.png) [@Punoqllads](https://boards.straightdope.com/u/Punoqllads)\ **Post date:** [January 14, 2015, 9:38pm UTC](https://boards.straightdope.com/t/area-under-a-curve-question/709835/17 "2015-01-14T21:38:30Z")
> [@beowulff](#): > > Don’t think of this as a graphing problem. > This is simply the sum (each month RMA’s - baseline). This. If values are in column V The sum under the baseline is the sum of min(V, baseline) The sum over the baseline is the sum of max(V - baseline, 0)