Great idea to use chatgpt to generate data! In regard to the true blanks I can't text because I use sheets, but I wonder if Excel allows you to use an empty array using {} and if that would work?
Teacher... Formula: =ArrayFormula(IFERROR(IF(MOD(ROW(C18:C29)-ROW(C18)+1,2)=0, C20:C29-INDEX(C19:C20, FLOOR((ROW(C18:C29)-ROW(C18)+1)/2)+1), C20:C29-INDEX(C18:C19, CEILING((ROW(C18:C29)-ROW(C18)+1)/2))))) The purpose of the formula is to calculate two running totals in one column, with the running total values alternating with each row. The formula achieves this by using the MOD(ROW(C18:C29)-ROW(C18)+1,2) function to check whether each row number in the range is even or odd. If a row number is even, the formula calculates the running total starting from that row by subtracting the value in the current cell from the value in the cell two rows below and then subtracting the value in the cell one row below from the result. If a row number is odd, the formula calculates the running total starting from that row by subtracting the value in the current cell from the value in the cell below and then subtracting the value in the current cell from the result. To retrieve the appropriate values from neighboring cells based on whether the row number is even or odd, the formula uses the INDEX function. The FLOOR and CEILING functions are used to calculate the appropriate index for the INDEX function, based on whether the row number is even or odd. To handle any errors that may arise in the formula, the IFERROR function is used to return a specific value in those cases. It should also be noted that the formula treats cell C18 as a distinct even absolute cell and cell C19 as a distinct odd absolute cell, while all other cells in the range are relative cells. This is because the formula depends on the position of the first row in the range to determine whether to start with an even or odd row number, and therefore the calculations for subsequent rows are relative to that initial row. The Formula should reside in Cell E19 instead of E20 utilizing the OFFSET Function, Accounts for Blank Cells in C18:C29 by not Breaking the Math Sequence, makes the Range Dynamic if Rows are Added or Subtracted, and Works in Google Sheets and can be Copied to other Locations within the Sheet. Notice the Formula should Lock Cell C18 and C19 as Absolute Cells and they need to be Updated Absolute Cells when Copied to a New Location/s. In summary, the formula is an alternating formula that calculates two running totals in one column based on whether the row number is even or odd. It uses the INDEX, FLOOR, CEILING, and IFERROR functions to facilitate the calculations. The formula is dependent on whether the first row in the range is even or odd. If the first row in the range is even, the formula calculates the running totals by subtracting values in pairs of cells, starting with cell C18 and alternating between subtracting the value in the cell above and the value in the cell two rows above. If the first row in the range is odd, the formula calculates the running totals by subtracting values in pairs of cells, starting with cell C19 and alternating between subtracting the value in the cell above and the value in the cell below. Values Range Results Range C18:C29: E20:E29: Cell: Values: Cell: Results: C17 Header E17 Header C18 179784.8 E18 Header C19 675.8 E19 Header C20 180011.3 E20 226.5 C21 902.1 E21 226.3 C22 180077 E22 292.2 C23 967.9 E23 292.1 C24 180277.3 E24 492.5 C25 1168.2 E25 492.4 C26 180533.3 E26 748.5 C27 1424.2 E27 748.4 C28 180670.9 E28 886.1 C29 1561.8 E29 886.0
Hello teacher good morning. Teacher i am stuck in a problem i made the invoice template as you shown in your invoice template video, everything i did as yiu said and it works fine. But teacher how do we store/record data as we store in your data entry form? I am unable to do this by my self. Pls add a video tutorial on this regarding invoicing template Thank you
![ОРБАН приехал к ПУТИНУ и "отчитался" о поездке в Киев 😁 [Пародия]](http://i.ytimg.com/vi/_0ukDovOWq8/mqdefault.jpg)
Teacher... Always Good Instruction, even Better with ChatGPT!!!
Thanks!
Great idea to use chatgpt to generate data! In regard to the true blanks I can't text because I use sheets, but I wonder if Excel allows you to use an empty array using {} and if that would work?
No, doesn't work, it doesn't allow empty arrays. Just ""
@@ExcelGoogleSheets ow, sad. Thanks! Yesterday I tried chat to generate tables and it's awesome!
Sir,
I want the interest amount for the 3rd year, for the fixed deposit of 5 years tenure . Is there any formula in this regard?
I believe CUMIPMT function should work for you.
This was really well done taking both aspects of Sheets/Excel and ChatGPT.
👍
Teacher...
Formula: =ArrayFormula(IFERROR(IF(MOD(ROW(C18:C29)-ROW(C18)+1,2)=0, C20:C29-INDEX(C19:C20, FLOOR((ROW(C18:C29)-ROW(C18)+1)/2)+1), C20:C29-INDEX(C18:C19, CEILING((ROW(C18:C29)-ROW(C18)+1)/2))))) The purpose of the formula is to calculate two running totals in one column, with the running total values alternating with each row. The formula achieves this by using the MOD(ROW(C18:C29)-ROW(C18)+1,2) function to check whether each row number in the range is even or odd. If a row number is even, the formula calculates the running total starting from that row by subtracting the value in the current cell from the value in the cell two rows below and then subtracting the value in the cell one row below from the result. If a row number is odd, the formula calculates the running total starting from that row by subtracting the value in the current cell from the value in the cell below and then subtracting the value in the current cell from the result. To retrieve the appropriate values from neighboring cells based on whether the row number is even or odd, the formula uses the INDEX function. The FLOOR and CEILING functions are used to calculate the appropriate index for the INDEX function, based on whether the row number is even or odd. To handle any errors that may arise in the formula, the IFERROR function is used to return a specific value in those cases. It should also be noted that the formula treats cell C18 as a distinct even absolute cell and cell C19 as a distinct odd absolute cell, while all other cells in the range are relative cells. This is because the formula depends on the position of the first row in the range to determine whether to start with an even or odd row number, and therefore the calculations for subsequent rows are relative to that initial row. The Formula should reside in Cell E19 instead of E20 utilizing the OFFSET Function, Accounts for Blank Cells in C18:C29 by not Breaking the Math Sequence, makes the Range Dynamic if Rows are Added or Subtracted, and Works in Google Sheets and can be Copied to other Locations within the Sheet. Notice the Formula should Lock Cell C18 and C19 as Absolute Cells and they need to be Updated Absolute Cells when Copied to a New Location/s. In summary, the formula is an alternating formula that calculates two running totals in one column based on whether the row number is even or odd. It uses the INDEX, FLOOR, CEILING, and IFERROR functions to facilitate the calculations. The formula is dependent on whether the first row in the range is even or odd. If the first row in the range is even, the formula calculates the running totals by subtracting values in pairs of cells, starting with cell C18 and alternating between subtracting the value in the cell above and the value in the cell two rows above. If the first row in the range is odd, the formula calculates the running totals by subtracting values in pairs of cells, starting with cell C19 and alternating between subtracting the value in the cell above and the value in the cell below. Values Range Results Range
C18:C29: E20:E29:
Cell: Values: Cell: Results: C17 Header E17 Header C18 179784.8 E18 Header C19 675.8 E19 Header C20 180011.3 E20 226.5 C21 902.1 E21 226.3 C22 180077 E22 292.2 C23 967.9 E23 292.1 C24 180277.3 E24 492.5 C25 1168.2 E25 492.4 C26 180533.3 E26 748.5 C27 1424.2 E27 748.4 C28 180670.9 E28 886.1 C29 1561.8 E29 886.0
Hello teacher good morning. Teacher i am stuck in a problem i made the invoice template as you shown in your invoice template video, everything i did as yiu said and it works fine. But teacher how do we store/record data as we store in your data entry form? I am unable to do this by my self. Pls add a video tutorial on this regarding invoicing template
Thank you
It would have to be separate. I recommend watching some videos covering relational database basics. That will lead you in the right direction.
hi