Login Page - Create Account

Support Board


Date/Time: Fri, 29 Mar 2024 10:12:32 +0000



[Programming Help] - Please confirm spreadsheet formula

View Count: 720

[2018-06-27 00:07:06]
User68474 - Posts: 195
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]
bradh - Posts: 846
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: 3952
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]
bradh - Posts: 846
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:

Login

Login Page - Create Account