Support Board
Date/Time: Mon, 25 Nov 2024 09:55:53 +0000
Spread Sheet formulas
View Count: 324
| [2024-03-13 21:19:13] |
| User312901 - Posts: 13 |
|
Hi! I'm trying to get some formulas to work. Obviously Sierra Spread Sheet is not working identically with Excel but is it possible to achieve something like this without defining all the rows individually?: =IF(AND(Sheet2!B4:Sheet2!B8 > Sheet2!AF4:Sheet2!AF8), IF(AND(Sheet2!E4:Sheet2!E8 > Sheet2!AF4:Sheet2!AF8), 1, 0), 0) (This is not working) Here I'm trying to return true if two conditions are met. First, values at B column needs to be bigger than values in AF column in all the rows inside the range. Second, values in E column needs to be larger than values in AF column respectively. A further goal would be to be able to determine the last row of the range by referencing to a number in another cell. So basically I would be having a cell where to type in the row number for range length. A lot appreciated if you could help me to crack this one. BR, MikkoS |
| [2024-03-13 21:33:54] |
| John - SC Support - Posts: 36238 |
|
Sierra Chart Spreadsheets do not have the ability to compare a range of data in the way you are wanting. What we would recommend would be to add a column that does the comparison and gives you a true/false (1/0) answer for each row. Then if you know the range of the data, do a sum over that range and see if it adds up to the range amount, if not then there is at least one value that does not meet the criteria. As to using a value from another cell, you can use the "INDIRECT()" function to get the value of that cell. You would then have to use this in your formulas to get the rest of the data as you want. For the most reliable, advanced, and zero cost futures order routing, use the Teton service: Sierra Chart Teton Futures Order Routing |
| [2024-03-14 10:44:10] |
| User312901 - Posts: 13 |
|
Hi! OK that is what I was suspecting. So now I have noe: =AND(Sheet2!B4 > Sheet2!AF4, Sheet2!E8 > Sheet2!AF4) copied down to several rows. This gives me 1 or 0 for each row. So now I can statically find whether certain range of rows all are true by using: =IF(SUM(Q1:Q5) = 5, 1, 0) or optionally: =AND(SUM(Q1:Q5) = 5) But can I compose the cell reference (Q5 in this case) by using a value from another cell? Basically I'm looking to alter number 5 both in cell reference and as a value we are looking the calculation to match to. |
| [2024-03-14 16:25:01] |
| John - SC Support - Posts: 36238 |
|
To use a cell as the reference value, you need to use a combination of CELL, CONCATENATE, and INDIRECT functions. For example the following: =SUM(K3:INDIRECT(CONCATENATE("K", CELL("contents", $S$3)))) This gives the sum of values in the K column from row 3 to the row defined in cell S3. Note that the word "contents" needs to entered in lowercase. The definitions of these functions is at the following link: Spreadsheet Functions For the most reliable, advanced, and zero cost futures order routing, use the Teton service: Sierra Chart Teton Futures Order Routing |
| [2024-03-15 09:20:45] |
| User312901 - Posts: 13 |
|
This works exactly like I wanted. Big thanks! |
| [2024-03-17 10:33:21] |
| User312901 - Posts: 13 |
|
BTW, Is there any way to link Sierra spread sheet with Excel? So that I could use Excel as background calculation and Sierra spread sheet would read cells from that Excel sheet. |
| [2024-03-18 15:31:47] |
| John - SC Support - Posts: 36238 |
|
There is not a built-in way to do this. You could programmatically set it up. Refer to the following to start: sc.GetSpreadsheetSheetHandleByName() List of Third Party Sierra Chart Study and System Programmers For the most reliable, advanced, and zero cost futures order routing, use the Teton service: Sierra Chart Teton Futures Order Routing |
To post a message in this thread, you need to log in with your Sierra Chart account: