Welcome to the Wikipedia Computing Reference Desk Archives
The page you are currently viewing is a transcluded archive page. While you can leave answers for any questions shown below, please ask new questions on one of the current reference desk pages.
This is a question about computing various statistics in Excel (average, total, median, mode, etc.). Let's say we have a column ... column "A". The column contains a list of numbers ... let's just say 120 values ... ten values for each of the 12 months. At the bottom of this column (specifically, in row number 121 ... that is, the row immediately after/below the 120th value of Column "A") ... I can easily set up a formula ... to calculate an average, median, mode, standard deviation, etc. Easy enough. Now, let's say that above each new month, I add in an extra row ... a row that says "January 2022" ... a row that says "February 2022" ... and so forth. This now screws up the ease with which I calculated an average ... (by simply typing a formula to calculate the average of the 120 preceding rows in the column). Now, with the revised spreadsheet ... there are 12 "extra" rows, interspersed throughout the list of values. Is there some easy way to calculate averages, totals, etc., while still keeping the Month Designation rows (and not having to remove them)? Hope my explanation makes sense. Thanks. In other words ... I am asking about the difference between calculating a formula (e.g., the average) of a bunch of continuous numbers in a column ... versus a bunch of non-continuous numbers in a column. Crude / rough / abbreviated example below.
Have you actually tried =AVERAGE(A1:A14)? Here's Microsoft's help on the AVERAGE function. It says "If a range or cell reference argument contains text, logical values, or empty cells, those values are ignored", which implies that it might average the 12 numbers (summing them and dividing by 12) while sensibly ignoring your Month Designation rows. Card Zero (talk) 02:05, 11 August 2022 (UTC)[reply]
Thanks. I will read that Help forum. No, I didn't even think to try the formula that you suggested. Whenever I have used a numerical formula ... and employed that formula on cell entries that are "words" -- instead of numbers -- I would always get a strange error ... like, #VALUE! ... or so. Thanks again ... I will try your suggestions. 32.209.55.38 (talk) 02:43, 11 August 2022 (UTC)[reply]
Yes, I may be misunderstanding that help page. It also says "text that cannot be translated into numbers cause errors". I was basing my interpretation of it on this unofficial Excel help page which shows SUM being used to total a column, automatically ignoring the cells containing text. The third suggestion there, IFERROR, also looks useful. Card Zero (talk) 09:13, 11 August 2022 (UTC)[reply]
Excel can store information well, it can do calculations well, and it can display information well, but it's not so good at doing more than one at a time. To human eyes, what you've done makes all the sense in the world, but to Excel you've muddied the data. Broadly speaking, what Excel "wants" is for the information to be laid out in tabular form, like this:
Month
Sales
January
24
January
39
January
77
January
81
January
48
You can then do formulas on the stuff in column B. Or, if you want to be fancier, you could summarize the data using Excel's pivot table function. They have a bit of a learning curve to them, but they offer a lot more flexibility in terms of displaying information. Matt Deres (talk) 12:38, 11 August 2022 (UTC)[reply]
Thanks, all. Yes, I understand all that was said above ... and I am basically well aware of Excel's oddities, quirks, and idiosyncrasies. Ultimately, the "lay-out" of the spreadsheet cannot be changed ... and it must remain as is ... with those Monthly Title designation rows interrupting Column A's flow of numbers (data). So, I guess I was asking ... is there some type of "complicated" / "fancy" formula? Something like =AVERAGE(A1:A6), =AVERAGE(A8:A13) ... ? I know that that specifically will not give me what I want. But, some type of formula that "combines" the intended column data (numbers), while disregarding the unintended column data (words)? If so, I imagine it would be a complicated, unwieldy formula ... but, once I get it right ... it's an easy matter of copy-and-paste. Something like: =AVERAGE(A1:A6) (A8:A13) (A15:A20) ... or who knows what? I can -- and will -- explore some "trial and error" in Excel. But, I assumed that this should be a relatively "common" issue ... that has a known answer. Also, I know that Excel formulas are extremely particular ... if you have to use a comma, or a colon, or a semi-colon, or parentheses, etc. So, I came to this Help Desk. Any thoughts? Side Note: Coincidentally, this just occurred to me. When I want to print a Word document ... I can select to print, say, Pages 1 through 6, skip pages 7 through 11, print pages 12 through 15, skip page 16, and print page 17, etc. And in the "Print Range" box, I type in something like: 1-6, 12-15, 17. And it works. And Word is from Microsoft ... so I was thinking perhaps Excel also has some little similar trick? Of course, getting the exact syntax is the issue. Thanks. 32.209.55.38 (talk) 15:11, 12 August 2022 (UTC)[reply]
Yes, you can pass a comma-separated list of ranges to the AVERAGE function. For example, AVERAGE(A1:A6,A8:A13,A15:A20). CodeTalker (talk) 17:35, 12 August 2022 (UTC)[reply]
