Numbers function for dynamically averaging the last 30 cells

Hi Braddon,

This would be a pretty easy task if the data to be summarised were in two columns of a single table, and had no gaps, but that does not appear to be the case.

Screenshots of two isolated sets of two rows of cells containing (par of) the data to be summarised, but giving no indication where this data will be found in the document does not provide enough information to begin finding a solution.

Where is the data stored? You say "data for each month." Is "each month" recorded on a different table? ..on a different pair of rows in the same table?

In the (very fuzzy) picture I have, the complicating factors will be gathering data from a continually changing subset of multiple tables on multiple sheets and which specific cell contains the thirtieth most recent datum and is the one at which to start the harvest.

Over to you for some details and clarification.

Regards,

Barry

"I want to create a function that takes the last 30 cells of data and creates a percentage so I can compare it to the total win percentage."

" it is important to get profit % for rows 31 and 32 separately."

For row 31, this month has only 1 (or 8) cells containing data.

For row 32, this month has only 19 cells containing data.

This implies that the count of the last 30 cells may begin at a different place on every 'row' (where 'row' means the year-long string of cells containing transaction records for one type of trade, distributed over many rows of the table).

The contents of row 31 imply that 30 cells of data may be spread through many more than three separate rows on the table.

What is the distinction between cells that appear empty and cells that contain 0? Are the 'empty' cells empty, or do they contain an invisible character or a null string?

Where does 'row 32' continue in May, June, July...?

Issue is becoming more clear.

Regards,

Barry

Hi Braddon,

Thanks for the details on "the last 30 cells."

Your response, "The last string of 30 cells will start on the same column and finish on a different column."

leaves me a bit unsure still.

By "different column," do you mean:

'The last string of 30 cells (in rows 31 and 32)will start on the same column and finish on a different column (from each other),' OR

'The last string of 30 cells (in rows 31 and 32)will start on the same column and finish on a different column (from the column in which they started).'

The first interpretation doesn't make sense to me, as "the last 30 cells" would end on the current date (or the previous trading day) for both periods.

The second interpretation will always be true except for periods of 30 trading days in the month following a month in which there were 29 trading days.

That interpretation implies that "last 30 cells" refers to the cells for the last 30 trading days, whether or not a trade was made, which fits the description in the post above, "Essentially I want the last 30 days of data averaged for both rows" if 'data' includes 'no trades on this day.'

Regarding cells containing zeros: Zeros are numbers, and will be included in counts of cells containing numbers and in averages calculated from cells including them. Placing a text value in those cells will remove them from calculations of averages and counts of numbers.

Regarding name change: Don't know if it's possible. Will pass the question along and see if I get a reply.

Regards,

Barry

One other question: How can I change my username?

Well, that one was easy. Click this link to go to the same question (and an answer).

Screen Name

Regards,

Barry

Hi Braddon,

Your 'where'd it go' alarm was going off at just the right time. 😁

I got back to this question today and made it work! Here's the sample file, which I've compressed to include only the rows needed for the June calculations,using data from the rows for April, May and June.

Row 1 contains labels for my own use—the day number for the maximum number of trading days in any month, and other short notes telling the purpose of the numbers in that column.

The actual number of trading days in a given month is in column Y of each row containing the list of trading dates for that month.

The calculations are done in the small block of occupied cells in columns AC - AI, with data from Y2, Y5 and Y8, and rows 4, 7 and 10.

Formulas:

Y2: COUNT(B2:X2)

Y5: COUNT(B5:X5)

Y8: COUNT(B8:X8)

These count the number of Trading dates in each month.

Y10: Initially placed in this cell, but replaced with the same formula in AC10. Y10 may be ignored.

AC10: COUNT(B10:X10) --counts the number of results recorded in the range B10-X10 (the current month)

AD10: MAX((AC10+Y5)−30,0) --Gets the number of results in the current month, adds the number of dates available in the previous month, subtracts 30 from the sum, then selects the larger of that result or zero. The result tells Numbers how many dates to skip at the beginning of row 7 (May)

AE10: MIN(Y2−(30−(AC10+Y5)),Y2) --Determines how many dates to skip in row 4 (April)

AC9: B --fixed entry. The current month count always starts on column B.

AD9: CHAR(66+AD10) --adds 66 to the number in AD10, then returns the letter with that ASCII code to show which column to start counting in the previous month (May)

AE9: CHAR(66+AE10) --Returns starting column for count in 2nd previous month (April)

AF10: COUNT(UNION.RANGES(FALSE,INDIRECT(AE9&"4:X4"),INDIRECT(AD9&"7:X7"),B10:X10))

--UNION.RANGES joins the search ranges for each of the months into a single range.

--INDIRECT constructs the ranges for rows 4 and 7 by adding the text to the start columns retrieved from AE9 and AD9

COUNT counts all numerical values in the joined ranges.

AG10: COUNTIF(UNION.RANGES(FALSE,INDIRECT(AE9&"4:X4"),INDIRECT(AD9&"7:X7"),B10:X10),"> 0")

--COUNTIF counts the numerical values that are greater than zero in the same range as used by COUNT

AH10: AG10÷AF10

--calculates the fraction of all results in the range that are 'wins'

--formatted to display as a percentage

AI10: (AF10−AG10)÷AF10

--calculates the fraction of all results in the range that are not 'wins'

--formatted to display as a percentage

Regards,

Barry

"I am still digesting this and trying to make the sample fit my real life spreadsheet."

Me too. 🙂

Simplification of expanding the solution over a wider date range and the full list of currency pairs should be possible, but getting there will take a block of time that I likely won't have available for a few days. Further comments, and possibly revised formulas, to come at that point

Regards,

Barry

"I incorrectly stated 22 days was the max trading days in a month and 23 is the max as in August."

Click on any cell in column X (the last column considered in the counts)

Go Table (menu) > Add Column Before.

Numbers will adjust the formulas as needed.

Counts will now end at column Y.

Tds (Trading Days) count for each month will now be in column Z, and formulas will reference the new location.

Etc.

Regards,

Barry