Support Board
Date/Time: Tue, 21 Jan 2025 04:04:20 +0000
[Programming Help] - Please confirm spreadsheet formula
View Count: 785
| [2018-06-27 00:07:06] |
| User68474 - Posts: 201 |
|
Is this the proper spreadsheet formula for summing and then averaging all the non-zero numbers, Rows G3:G1418 in Column G of a Spreadsheet Study? AVERAGE_IGNOREZEROES(G3,G1418) Furthermore, is the proper place to enter this Formula(or the corrected one): " In one of the available Spreadsheet formula columns, by default K through Z" (pasted from documentation) ?? If so, what cell? (i.e. (X2)? |
| [2018-07-18 21:17:07] |
| ForgivingComputers.com - Posts: 987 |
|
Yes, that is the correct formula, however it will need an = sign in front of it. Normally spreadsheet formulas go in row 3 of columns K-Z (or higher if you add more columns in the study inputs), which represents the current bar. Row 4 is the one before that, etc. If you put it in row 3 of one of the columns K-Z, it will copy itself down to the last row, modifying the numbers, so if: K3: =AVERAGE_IGNOREZEROES(G3,G1418) K4: =AVERAGE_IGNOREZEROES(G4,G1419) K5: =AVERAGE_IGNOREZEROES(G5,G1420) etc. If you don't want it to do that, then put a $ in front of the row number: =AVERAGE_IGNOREZEROES(G$3,G$1418) If only need the formula once, and you don't need one per row, you can use any cell in Column H to hold the formula. |
| [2018-07-18 22:19:01] |
| Sawtooth - Posts: 4157 |
|
This will give you the average of only 2 cells, G3 and G1418: =AVERAGE_IGNOREZEROES(G3,G1418) This will give you the average of all cells from G3 to G1418 inclusive: =AVERAGE_IGNOREZEROES(G3:G1418) |
| [2018-07-18 22:29:27] |
| ForgivingComputers.com - Posts: 987 |
|
Thanks Tom. Missed that part of the description. Brad |
To post a message in this thread, you need to log in with your Sierra Chart account: