Works On: Windows 7,Vista,XP- Mac OS X In this video tutorial for Microsoft Excel 2011 For Mac, expert author Guy Vaccaro teaches you to effectively utilize the features and functions of Excel through project based learning.
MS Excel 2011: Basics (Tutorial Complete) Congratulations, you have completed the Excel 2011 tutorial. Tutorial Summary Excel 2011 is a version of Excel developed by Microsoft that runs on the Mac platform.
This Excel 2011 tutorial covered the basic concepts of spreadsheets such as:. Cells. Rows.
Columns And then expanded on the more advanced Excel topics such as:. Sheets. Hyperlinks. Ranges Each version of Excel can 'look and feel' completely different from another. As such, we recommend that you try one of our other Excel tutorials to become familiar with the Excel version that you will be using. Other Excel Tutorials Now that you know the basics of Excel 2011 for Mac, learn more. Try one of our other Excel tutorials.
In recent years, researchers and practitioners in the behavioral sciences have profited from a growing literature on delay discounting. The purpose of this article is to provide readers with a brief tutorial on how to use Microsoft Office Excel 2010 and Excel for Mac 2011 to analyze discounting data to yield parameters for both the hyperbolic discounting model and area under the curve. This tutorial is intended to encourage the quantitative analysis of behavior in both research and applied settings by readers with relatively little formal training in nonlinear regression. The goal of this technical article is to present researchers with a tutorial on how to conduct both nonlinear regression and area under the curve (AUC) estimations using Microsoft Office Excel 2010 and Excel for Mac 2011 (hereafter referred to simply as Excel) to analyze delay-discounting data.
Analyses of delay discounting (the process by which delayed rewards have lower subjective values than sooner rewards of the same magnitude) have become increasingly popular due to their ability to allow researchers to investigate the temporal properties of reward for socially important problems (see; ). Specifically, this operationalization of decision making is translated easily to issues concerning impulsivity and self-control. For instance, behavior analysts have come to define a form of impulsivity as preference for a smaller sooner reward, despite a larger delayed alternative being available (see ). This behavioral interpretation of impulsivity has led to numerous advances in clinical issues such as increasing tolerance to reinforcement delay in individuals with acquired brain injury or autism (; ), as well as in the general assessment of impulsivity in children with attention deficit hyperactivity disorder (ADHD;; ), serious emotional disturbance (e.g., ), and severe problem behaviors (e.g., ).
Collecting Discounting Data The commonly used procedure for studying discounting is the administration of a series of hypothetical monetary choice trials (see; ). Typically, hypothetical monetary choice paradigms feature a titrating series of choices between smaller sooner rewards (SSRs) and a larger later reward (LLR). The point at which an individual switches from choosing the LLR to the SSR is commonly termed the indifference point. This indifference point serves as an estimation of the subjective value of the LLR after some delay. This methodology then is replicated across numerous delay values (i.e., the delay until the receipt of the LLR) to arrive at an understanding of the individual's preference for reward over time (the reader is encouraged to consult, for a lengthier discussion on discounting assessment methodology). As the reader may surmise, the employment of hypothetical choice, as well as the use of verbal self-report, is a substantial deviation from the direct measurement techniques usually used in behavior-analytic studies. Nevertheless, numerous studies have demonstrated that values obtained from hypothetical choices correlate with values obtained from actual rewards (see;;; ).
The parameter k is a free parameter that denotes the degree of discounting (or the discounting rate) observed in the data path (i.e., steepness of the curve or how fast the value drops as a function of delay). Parameter k is thus the dependent variable in quantitative studies of discounting. Higher k values translate to higher degrees of discounting or impulsivity. To determine k, the researcher must plot the subjective values against reward delays. The researcher then uses least squares nonlinear regression to fit a curve to the data points. Describing participant patterns of discounting presents numerous analytical challenges for researchers who are unfamiliar with advanced statistics because of reliance on nonlinear regression models.
Although a full understanding of how nonlinear regression works requires mastery of matrix algebra, the basic method is relatively easy to follow. First, the researcher is required to enter the numerical value of the dependent variable at each level of the independent variable (or the subjective value of the LLR or indifference point at each delay value in the case of delay discounting) into the quantitative model. Next, a best fit line (or curve) is generated according to those initial values. Specifically, a best fit line or curve is generated by estimating a free parameter (e.g., k) within the model until the vertical difference between each observed data point and the estimated point (called the residual value) on the line is minimized to the smallest possible value (see ).
In particular, the regression process calculates the smallest grand sum of the squared residuals, which is why this process is termed least squares regression (for a thorough discussion on this topic, see ). Through this iterative line curve-fitting process, the free parameter value (e.g., k in discounting models) is identified. Because of this iterative process, nonlinear regression is most efficiently and accurately completed with the aid of computer software. Unfortunately, nonlinear regression programs are substantially more expensive than Excel.
For instance, GraphPad Prism is a program frequently cited in discounting literature due to its relative ease of use in conducting extremely precise nonlinear regression analyses. GraphPad Prism costs approximately $450 for one academic license ($300 for one student license; ), whereas a full-version Excel 2010 costs only $140. Moreover, many agencies and institutions supply users with complimentary copies of Excel or offer the program at greatly reduced prices. Thus, the focus of the remaining technical article is on using Excel to conduct discounting analyses. Note that we restrict the scope of this paper to the hyperbolic model and AUC approaches for the sake of parsimony.
A depiction of how residuals are utilized in nonlinear regression. Despite the advantages of describing discounting using a quantitative model, the reader should be advised that limitations exist with such analyses. For example, numerous preference reversals could prohibit statistical software from fitting a line to the data. Also, using nonlinear regression merely estimates a description of the participants' decision-making tendencies. For those who wish to analyze data without necessarily conforming to the assumptions of theoretical models or using nonlinear regression, proposed that AUC may be used to estimate degrees of discounting.
Using AUC may be especially advantageous in novel applications or translational research in which theories of decision making or impulsivity are not necessarily the focus of study (i.e., the researcher simply wants to use AUC as a dependent variable without theorizing models of decision making or impulsivity). To conduct AUC calculations, the analyst plots indifferent points on the y axis as a function of delay plotted along the x axis.
In this technique, lines are drawn connecting data points, with vertical lines drawn from the x axis to each indifference point to generate a series of trapezoids. For example, in, a series of trapezoids were generated based on hypothetical discounting data (see the case example presented below). The inset of depicts the trapezoid generated between delays of 270 and 520 days.
A step-by-step depiction of this process applied to the trapezoid between 270 and 520 days is shown in. The area of the trapezoid is determined by calculating the height of each side of the trapezoid (Step 1; top panel of inset), calculating the width of the trapezoid (Step 2; middle panel of inset), and multiplying the average of the two heights by the width (Step 3; bottom panel of inset). Using this technique, the analyst can then sum the areas of all the derived trapezoids to yield an AUC estimate. The equation for this analysis is.
Where x 1 and x 2 are successive delay value, and y 1 and y 2 are the subjective values or indifference points associated with those delays. Thus, the total AUC negatively correlates with k values derived from the quantitative models discussed above, such that steep discounters (i.e., relatively impulsive responders) with high k values will have low AUC. Finally, to calculate a total proportion of AUC, the summed AUC is divided by the total possible AUC. The total possible AUC is equal to the maximum delay (width) multiplied by the undiscounted amount (height). Practical Utility of a Quantitative Analysis of Discounting Notwithstanding the clinical interest in the promotion of self-control and the advantages of using discounting analyses to understand socially important behaviors (see ), only four articles to date in JABA (only 0.78% of those in PsycINFO) have explicitly examined discounting using conventional quantitative methods (;;; ). PsycINFO searches of self-control and impulsivity yielded an additional 36 relevant articles in JABA.
Thus, although behavior analysts are interested in these topics, the majority of these studies did not employ assessments of discounting in their procedures. Which behavioral variables could account for the relatively low number of submissions using quantitative models of discounting to JABA? Hypothesize that applied behavior analysts may be hesitant to use such procedures because they lack formal training in such methods or view the use of equations as an overly complicated and difficult contribution to the analysis of operant behavior. Although the methods to obtain discounting parameters or estimates are indeed seemingly complex due to nonlinear regression models, they may be completed through the aid of spreadsheet applications such as Excel. The purpose of this paper is to provide a rudimentary review of how these calculations are performed, and to provide a task analysis to aid the reader in creating an Excel-based calculator for use in analyzing discounting data. The procedures we detail in this article supplement those provided by Dallery and Soto in a recent workshop at the Association for Behavior Analysis International (2010). We hope that the provision of these analytical methods will encourage researchers to conduct quantitative discounting analyses in an effort to add further breadth to this growing literature.
Moreover, we hope that this article will help to ease readers' concerns that they lack the quantitative training or ability to conduct such analyses, because we present the reader with the means to analyze such data. Preparing researchers and clinicians to translate from quantitative models (e.g., in the present case, discounting) will further our field's mission to contribute to the broader behavioral sciences by achieving “innovation through synthesis” (, p. 296). That is, modeling the discounting phenomenon and integrating these findings with those from other disciplines to address concerns of the human condition will help researchers and practitioners to improve measurement of the processes that underlie choice. Specifically, in the case of discounting, there is great potential for behavior analysts to affect diverse disciplines due to the increasing interest in discounting by behavioral, cognitive, and social psychologists (e.g.,; ), as well as behavioral (e.g., ) and neuro-economists (e.g., ). A Case Example Here is a scenario in which a behavioral scientist might find the tutorial useful.
You are a researcher interested in determining whether a behavioral account of impulsivity (i.e., discounting) can predict sixth-grade students' performance on commonly used continuous performance tasks in the diagnosis of ADHD (e.g., the Gordon Diagnostic System GDS, 1983, and the Conners Continuous Performance Test II Version 5 CPT-II, 2004). For the discounting portion of your study, you plan to administer a child-adapted discounting procedure (e.g., ) to derive the discounting parameter k, as well as AUC. To determine the predictive validity of the discounting task for use in ADHD screenings, you will compare the derived discounting scores ( k and AUC) with scores obtained from the GDS and the CPT-II Version 5.
For the sake of this tutorial, we will focus only on how to derive the discounting parameters. As described above, the discounting parameters are derived from the participants' series of choices between an SSR (values less than $100 that adjust following each response; see, for examples of this adjustment procedure) and an LLR (in this example, always $100) across varying lengths of delay (i.e., the delay until the receipt of the LLR).
In this case, you choose the delay values (in days; adapted from ) of 1, 5, 30 (i.e., 1 month), 60 (i.e., 2 months), 180 (i.e., 6 months), 270 (i.e., 9 months), 520 (i.e., ≈1.5 years), and 1,460 (i.e., 4 years) days. The observed subjective values yielded by your assessment for one participant are 99, 70, 60, 50, 30, 20, 7, and 1, respectively (across delays of 1 day to 1,460 days). The remainder of this article will discuss the procedures to derive discounting scores ( k and AUC) for this participant (and conceivably others).
Next, recreate the data from the case example (described above) in the workbook by inputting the delay values in days (only the numerical values; i.e., 1, 5, 30, 60, 180, 270, 520, 1460; do not include the word “days”) in Column A (Cells A2 through A9) and the subjective value of $100 at each delay in Column B (Cells B2 through B9; 99, 70, 60, 50, 30, 20, 7, and 1, respectively; do not include the $ sign). Note that these values will change depending on the researcher's experimental questions and dependent variables. In Cell C2, type “ = IF(ISNUMBER(MyData!A2),MyData!A2,“”)” and press ENTER. The command “IF(ISNUMBER” tells Excel to predict only a subjective value if a delay value is entered into the referenced cell. Note that “” consists of open and closed double quotes (“”) with no space between the two. This will be employed throughout this tutorial. The number “1” should now appear in Cell C2.
Click on Cell C2, and then place the mouse cursor over the small black square in the lower right corner of Cell C2 until the cursor becomes a cross. Click and hold the left mouse button down on the small black square. Drag the cursor down to Cell C101 and then release. This process is known as “drag and drop” and will be referenced as such throughout the remainder of this tutorial. Using this command, the calculator will conduct analyses based on 100 possible delay values. SUMMARY The Microsoft Office Excel spreadsheet program has utility in various behavior-analytic activities, ranging from single-subject design graphing (; ) to more complicated procedures such as conducting matching analyses and generating reinforcement schedules.
In this article, we have described two simple ways to perform traditional delay discounting analyses to assist readers. Given the growing interest in self-control/impulsivity assessment and treatment, as well as in the processes that underlie choice and discounting, we hope this tutorial encourages researchers to use discounting models as an additional analytic tool in self-control or decision-making research. Many interesting extensions of discounting are possible, and the field remains wide open for such innovative translation. Understanding population differences is an important step in developing behavioral models of impulsive behaviors. A potential advantage to this approach is that quantitative models may lead to insights for more sophisticated treatments that would not otherwise be achieved (see ). To date, only one study in JABA has investigated discounting with children ; thus, more work in this area is warranted.
Likewise, using the quantitative measures yielded by discounting analyses, behavior analysts can offer a scientifically valid pre- and posttreatment measure to evaluate the effects of behavioral interventions (as in ). Moreover, discounting parameters such as k or AUC are understood by a wide range of social scientists outside behavior analysis, offering behavioral researchers a metric that has transdisciplinary utility that ultimately may increase the impact of their research. For researchers interested in other modalities of discounting, please note that only slight modifications to the current spreadsheet are necessary to perform probability discounting analyses (see; ). 1For a discussion on the differing quantitative models of discounting, the reader is encouraged to consult. 2Often, researchers are interested in the differences in discounting between special populations and the norm (e.g., gamblers vs. Nongamblers, substance abusers vs. Nonsubstance abusers, etc.) or across parametric variables (e.g., age, income, etc.).
In such cases, the researcher may want to fit the discounting model to an entire group's data set. A commonly used approach to model group discounting is to calculate the median subjective value of the group at each delay. The median values then may be analyzed as if they were obtained from an individual participant.
For the sake of this calculator, the median values of the group may be entered into the Subjective Value column on the “MyData” tab in the initial stages of analysis. Unfortunately, designing an Excel calculator to analyze all participants simultaneously is outside of the scope of this tutorial.
For large-group analyses that warrant an efficient means of analysis, we recommend the reader use GraphPad Prism 5. 3This calculator was validated using the raw data and derived discounting parameters reported in. Specifically, we reanalyzed the data in Table 1 in Dixon et al. Using our calculator, and found the same results for all participants. We then reanalyzed these data using the nonlinear regression function in GraphPad Prism 5, and yielded discounting parameters equal to those derived from our Excel calculator.
Carrots and sticks: Unlock the power of incentives to get things done. New York: Bantam Books; 2010.
Bancroft S.L, Bourret J.C. Generating variable and random schedules of reinforcement using Microsoft Excel macros. Journal of Applied Behavior Analysis. 2008; 41:227–235.
Carr J.E, Burkholder E.O. Creating single-subject design graphs with Microsoft Excel.
Journal of Applied Behavior Analysis. 1998; 31:245–251.
Conners K.C. Conners continuous performance test (2nd ed.) Toronto, Canada: Multi Health Systems; 2004. Critchfield T.S, Kollins S.H.
Temporal discounting: Basic research and the analysis of socially important behavior. Journal of Applied Behavior Analysis. 2001; 34:101–122. Critchfield T.S, Reed D.D. What are we doing when we translate from quantitative models. The Behavior Analyst. 2009; 32:339–362.
Dallery J, Soto P.L, Hackenberg (Chair) T.D. Practicum on quantitative methods and data analysis. Science board sponsored event. May, Special event conducted at the meeting of the Association for Behavior Analysis International, San Antonio, TX.
Dixon M.R, Cummings A. Self-control in children with autism: Response allocation during delays to reinforcement. Journal of Applied Behavior Analysis. 2001; 34:491–495. Dixon M.R, Falcomata T.S. Preference for progressive delays and concurrent physical therapy exercise in an adult with acquired brain injury.
Journal of Applied Behavior Analysis. 2004; 37:101–105. Dixon M.R, Holton B. Altering the magnitude of delay discounting by pathological gamblers. Journal of Applied Behavior Analysis.
2009; 42:269–275. Dixon M.R, Jackson J.W, Small S.L, Horner-King M.J, Lik N.M.K, Garcia Y, et al.
Creating single-subject design graphs in Microsoft Excel 2007. Journal of Applied Behavior Analysis. 2009; 42:277–293.
Dixon M.R, Jacobs E.A, Sanders S. Contextual control of delay discounting by pathological gamblers.
Journal of Applied Behavior Analysis. 2006; 39:413–422. Dixon M.R, Marley J, Jacobs E.A. Delay discounting by pathological gamblers.
Journal of Applied Behavior Analysis. 2003; 36:449–458.
Gordon M. The Gordon diagnostic system.
DeWitt, NY: Gordon Systems; 1983. Green L, Myerson J, Ostaszewski P. Amount of reward has opposite effects on the discounting of delayed and probabilistic outcomes.
Journal of Experimental Psychology: Learning, Memory, & Cognition. 1999; 25:715–723. Hoerger M.L, Mace F.C. A computerized test of self-control predicts classroom behavior. Journal of Applied Behavior Analysis.
2006; 39:147–159. Johnson M.W, Bickel W.K. Within-subject comparison of real and hypothetical money rewards in delay discounting. Journal of the Experimental Analysis of Behavior. 2002; 77:129–146.
Lagorio C.H, Madden G.J. Delay discounting of real and hypothetical rewards: III. Steady-state assessments, forced choice trials, and all real rewards. Behavioural Processes.
2005; 69:173–187. Mace F.C, Critchfield T.S.
Translational research in behavior analysis: Historical traditions and imperative for the future. Journal of the Experimental Analysis of Behavior. 2010; 93:293–312. Madden G.J, Begotka A.M, Raiff B.R, Kastern L.L. Delay discounting of real and hypothetical rewards.
Experimental and Clinical Psychopharmacology. 2003; 11:139–145. Madden G.J, Bickel W.K, editors. Impulsivity: The behavioral and neurological science of discounting. Washington, DC: American Psychological Association; 2010. (Eds.).
Madden G.J, Johnson P.S. A delay discounting primer. In: Madden G.J, Bickel W.K, editors. Impulsivity: The behavioral and neurological science of discounting. Washington, DC: American Psychological Association; 2010.
(Eds.). Madden G.J, Raiff B.R, Lagorio C.H, Begotka A.M, Mueller A.M, Hehli D.J, et al.
Delay discounting of potentially real and hypothetical rewards: II. Between- and within-subject comparisons. Experimental and Clinical Pharmacology. 2004; 12:251–261. Mazur J.E. An adjusting procedure for studying delayed reinforcement. In: Commons M.L, Mazur J.E, Nevin J.A, Rachlin H, editors.
Quantitative analyses of behavior: Vol. 5. The effect of delay and intervening events on reinforcement value. Hillsdale, NJ: Erlbaum; 1987. (Eds.). McKerchar T.L, Green L, Myerson J, Pickford T.S, Hill J.C, Stout S.C. A comparison of four models of delay discounting in humans. Behavioural Processes.
2009; 81:256–259. Motulsky H, Christopoulos A. Fitting models to biological data using linear and nonlinear regression: A practical guide to curve fitting. New York: Oxford University Press; 2004. Myerson J, Green L. Discounting of delayed rewards: Models of individual choice.
Journal of the Experimental Analysis of Behavior. 1995; 64:263–276.
Myerson J, Green L, Warusawitharana M. Area under the curve as a measure of discounting. Journal of the Experimental Analysis of Behavior. 2001; 76:235–243.
Neef N.A, Mace F.C, Shade D.B. Impulsivity in students with serious emotional disturbance: The interactive effects of reinforcer rate, delay, and quality. Journal of Applied Behavior Analysis.
1993; 26:37–52. Neef N.A, Marckel J, Ferreri S.J, Bicard D.F, Endo S, Aman M.G. Behavioral assessment of impulsivity: A comparison of children with and without attention deficit hyperactivity disorder. Journal of Applied Behavior Analysis. 2005; 38:23–37. Rachlin H.
Judgment, decision and choice: A cognitive/behavioral synthesis. New York: Freeman; 1989. Rachlin H, Raineri A, Cross D.
Subjective probability and delay. Journal of the Experimental Analysis of Behavior. 1991; 55:233–244. Reed D.D.
Using Microsoft Office Excel 2007 to conduct generalized matching analyses. Journal of Applied Behavior Analysis. 2009; 42:867–875. Reed D.D, Martens B.K.
Temporal discounting predicts student responsiveness to exchange delays in a classroom token system. Journal of Applied Behavior Analysis. 2011; 44:1–18. Schweitzer J.B, Sulzer-Azaroff B. Self-control: Teaching tolerance for delay in impulsive children. Journal of the Experimental Analysis of Behavior.
1988; 50:173–186. Vollmer T.R, Borrero J.C, Lalli J.S, Daniel D. Evaluating self-control and impulsivity in children with severe behavior disorders. Journal of Applied Behavior Analysis. 1999; 32:451–466. Waltz T.J, Follette W.C. Molar functional relations and clinical behavior analysis: Implications for assessment and treatment.
The Behavior Analyst. 2009; 32:51–68.