Calculate a value using previous cell in same column with arrayformula or byrow in Google Sheets - is it possible?
I have a very simple formula for calculating portfolio value in Google Sheets that I'd like to automate (so the calculation fills automatically as new lines are added), but I can't seem to find a way to reference a previous cell in the same column using arrayformula or byrow. I keep getting a circular reference (which I understand) but just can't seem to find a workaround. Can anyone help, or perhaps put me out of my misery by letting me know it's just not possible (because of the way arrays & byrow work). Or, if there is another way?
Here's the data ($100,000 is in first value entered into cell B1, calculations are done in subsequent cells B2,B3,B4 etc...):
Appreciate any and all help!
Solution
Use SCAN:
=SCAN(B1,A2:A5,LAMBDA(a,c,a*(1+c)))
- How to extract the whole match and not just the captured group?
- Custom sort order according to cell value
- Google Apps Script creates sheets version of excel file
- How to fix a #VALUE error when trying to pull an entire row of information from multiple sheets
- If var isnumber, for script
- Excel spill range reference in Google sheets
- Google Sheets Script new Date() with timezone is providing wrong time
- Trying to Email Multiple PDF Blobs in Sheets Javascript
- HTTP Google Sheets API v4 how to access without OAuth 2.0?
- List All Workdays between Start and End Dates Displaying Associated Name and Details in Other Columns
- How do I edit the response of a form submission in Google Forms/Sheets?
- How can I count repeats in a a column but reset count on non-repeats, in an array formula
- How do I stop Google AppScripts (Sheets) from creating two protected range entries
- Transfer Data from one sheet to another
- googlesheet get last non blank row from specific rows
- Send Mail after Form Submit to Google Sheet
- IFS with multiple conditions check in Google Sheet
- Can I find the value of a cell B49 that changes value according to the value of the cell F2 ( in Google sheet)
- What is the GoogleJsonResponseException error when trying to get Drive.comments.list()?
- Google Sheets: extract text between two characters
- Google Sheets REGEXEXTRACT to extract text value for URL query-parameter
- How to access the cell next to the selected cell in Google sheet using script?
- How to make the cancel Button work in Google App Script Browser Input Box
- Dowload public Google Spreadsheet
- Index Match using Tables in Google Sheets not giving correct results
- How to extract url from Google Sheet Cell with anchor text hyperlink
- How do you deal with multiple values in a single cell separated by comma?
- How to send content as comment to a specific cell in google sheets
- Get Google Sheet by ID?
- Excel getting names from biggest values with Regextract