braddonfromboulder
Level 1
(4 points)
Q: Numbers function for dynamically averaging the last 30 cells
I am a trader and have many months of data. I have a percentage for the sum of all wins and losses per currency pair currently. 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.
The cells are blank unless I add data in either to top or bottom row. 1's are winners and -1's are losses. 0's means it did not set up.
Here is a full month with 20 trading day cells of data (max cells in a month are 22):
This is the current month:
I have 16 month of data with win percentages for all of my data. As you see, I also have winning monthly winning percentages easily in the above examples (with a little division). I really, really want to compile the data from the last 30 cells with data to create a winning percentage. It would need to sometimes include 3 different lines from different months of data to create 30 cells with a number in it.
A friend has used the COUNTIF command but I did not get that to work for me.
If there is a different website to ask this detail of question please share that too.
Thanks in advance,
B
MacBook, macOS Sierra (10.12)
Barry
Level 7
(32,714 points)
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
