Auto averaging series of formula results in Numbers

Question

Level 1

13 points

Auto averaging series of formula results in Numbers

In Numbers v11.1: six columns. A & B formatted as Text. C through F formatted as Currency. C & D are straight data entry. C is positive, D is negative. E is a running balance formula auto sum result of current row C and D and previous row E. These seem to function correctly. Sheet rows are separated into years and # of rows per year varies by year, but never more than 35. Column F seems to be the culprit. The last row of each "block" of yearly data entry is used as a "totaling row". C & D for that row are auto sums for the previous column entries for that year. (I'm working with a four year section of the spreadsheet for troubleshooting purposes) Column E for this "totaling row" is blank, since E is used to represent a "running balance" of all years contained in the sheet. Column F is a formula auto sum for each year, using the totals derived via auto sum of C & D. My problem occurs when I try to auto average all the data in column F (which, I my troubleshooting mode of four years represents 4 cells of data). Auto average is way low. When I enter the values of these four cells as straight data entry, and then auto average, the result is correct. I suspect there's something about auto averaging formula results that may be a play, but I don't know enough to troubleshoot further. Help if you can. Sorry about the convoluted saga, but...

[Re-Titled by Moderator]

MacBook Pro 13″, macOS 10.15

Posted on Oct 30, 2023 6:41 AM

Reply

Answer 1

Top-ranking reply

Recycleur

Level 4

1,663 points

Oct 30, 2023 1:31 PM in response to 468Daddy

I don't think it's resolved at all. The average of 3182.62, 3884.86, 2206.25 and 1701.88 is 2743.90, not 2801.66. The sum is 10975.61.

If you draw a big rectangle around the cells you want to average you might be including hidden rows, too bad we cannot see those numbers on the left in your picture.

Also the fact that typing 497.44-3680.06 gives a different result than C64-D64 is a big red flag. The values shown are not the actual values in the cells, formatting is playing some dirty tricks.

Reply

Answer 2

468Daddy Author

Level 1

13 points

Oct 30, 2023 1:17 PM in response to 468Daddy

SG-The issue has resolved. I can't say I fixed it, 'cause I've no idea what changed (except the results, obviously). In each of the "totaling rows" I deleted all the yearly amounts in column F that were determined by the Numbers default averaging formula (four cells) and entered the numerical values of the cells in columns C64 & D64. Eg, at F64, instead of just using the Numbers formula to get the result of 3182.62 by clicking on C64-D64; I entered <497.44-3680.06> in the F64 cell formula option (press = and enter data). The result is the same, but when I select the four F column cells for auto averaging, I get the correct result, 2801.66. Magic. Thanks for your input. Is there a star or a thumb I can give you?

Reply

Answer 3

SGIII

Level 8

37,562 points

Oct 30, 2023 7:20 AM in response to 468Daddy

A picture can be worth a lot of words. To post a screenshot: shift-command-4, drag to select area, release, start new post here and use the mountains-and-moon 'Image insertion' icon beneath the compose window to insert the screenshot image from the Desktop.

SG

Reply

Answer 4

SGIII

Level 8

37,562 points

Oct 30, 2023 9:14 AM in response to 468Daddy

What formula are you using to calculate the average?

Also you might consider a table for each year. That will simplify formulas. Your current table layout is "Exelsy". To see efficient document designs for Numbers you could consider having a look at the templates at File > New in your menu.

SG

Reply

Answer 5

468Daddy Author

Level 1

13 points

Oct 30, 2023 4:05 PM in response to Recycleur

Here's the reduced sheet in two parts. I usually would do this using one tab per year, but it went on longer than anticipated and I'd already gone into year #2, so....

Anyhow, I'm thinking that may be the road to go at this point.

Reply

Answer 6

Badunit

Level 7

30,726 points

Oct 30, 2023 6:32 PM in response to 468Daddy

The average of the four numbers you provided is -2801.66, as you say. But if you change the sign on -2206.25, making it positive, and then do the average you get the other answer of -1698.53. I suspect there is something going on with that cell/formula and it is a positive number formatted in red rather than a negative number like the others (which I had assumed were formatted to display negatives as red but now I am wondering if they are manually set to red).

Reply

Answer 7

468Daddy Author

Level 1

13 points

Oct 30, 2023 1:58 PM in response to Recycleur

Oh, man….

Is this where we jump to Excel?

i’m traveling right now. I’ll have to look at this much closer when I get home. hopefully it’s user error and not programming or else it does not bode well for AI…

Reply

Answer 8

468Daddy Author

Level 1

13 points

Oct 30, 2023 4:25 PM in response to Recycleur

Recycleur:

Your math is correct, your selection of addends is wrong. The 1701.88 represent 2023, which is incomplete as of today, and the first addend should have been 1932.90 for the year 2019....which I realize you can't see because of my poor cropping. If you swap 1932.9 for 1701.88 in your string, the result 11206.63, which divided by four is 2801.66. Sorry for the poor foundation....it all seemed so simple at the inanition of this thread....

Reply

Answer 9

468Daddy Author

Level 1

13 points

Oct 30, 2023 8:15 AM in response to SGIII

Disregard G, H, and I....they are my test areas. I appreciate your jumping in on this, I'm hoping it's something silly...

Reply

Answer 10

468Daddy Author

Level 1

13 points

Oct 30, 2023 8:19 AM in response to 468Daddy

This might be better. The two cell in lower right represent column averages. the 16xx.xx is the Numbers generated average, the 28xx.xx is the actual, mathematically derived average.

Reply

Answer 11

468Daddy Author

Level 1

13 points

Oct 30, 2023 10:38 AM in response to SGIII

There’s a default option at the bottom of the spreadsheet that appears whenever you select a series of cells.

Reply

Answer 12

468Daddy Author

Level 1

13 points

Oct 30, 2023 6:32 PM in response to Badunit

Hadn't thought of that one (one of many). I'll go over the sheet tomorrow. Brain fried for tonight.....

Thanks so much for the assist.

Reply