<div>
Hi to funny stuff in your formula define for cell E45 is "#ALL"<br><br>=SUM(DATANEW[[#All];[TSB]]), I transformed the formula into this one =SUM([TSB])<br><br>and the behaviour now is fine to me. Do I have mist some thing ?
</div>


<div>
Hi Louis, My example file is wrong, my apologies, I will correct and change it. The formula in that cell should be the amount from one column to the right and one cell up. i.e. 114,078.83 For example =INDEX([fieldname,ROW()-1)<br>I will edit my opening question.<br>(This is exactly the same as the value you have obtained, but acquired in a different way). So you have not missed something, I have.<br>I will change my example file shortly.<br>Anthony
</div>


<div>
I THINK my problem is caused by ROW() counting the number of rows in the sheet, whereas INDEX(etc) uses the number of rows in the range defined by fieldname, so when I subtract 1, I am subtractiing one from the sheet row number NOT from the field range row number.&nbsp;&nbsp;
</div>


<div>
=row() is returning the row number where formula is define<br><br>if in A5 formula is =row() cell value will be '5'
</div>


<div>
<pre><code class="code-1 nolinks" id="code-20-43068317-1"><span>=<a href="https://exceljet.net/excel-functions/excel-index-function" rel="ugc">INDEX</a>(A1:A5,2) // returns Value of cell A2</span>
</code></pre>&nbsp;I trying to find out what you want to do for example above if you got 0 instead of 2 excel will fire an error.
</div>


<div>
Hi, I am not familiar with Table formulas so what I am going to change to the formulas will be in terms of regular excel terms. You can find the table equivalents yourself.<br><br>A20: This is referring to row()-1 which is 19 whereas the number of rows in the table is 18. What you really need is the row before the 18th. So change the formula to =INDEX([HSBCBal],ROW()-3)<br><br>B20: The second part of the formula here is referring to @HSBC which is the last row of the formula. This part also should be referring to the previous row. <br>Change the formula to &nbsp;=-SUM(INDEX([HSBC],1):OFFSET([@HSBC],-1,0))&nbsp;
</div>


<div>
Actually in your previous file there were two problems. One was corrected by Louis (=SUM([TSB]) ). The other was column B where the reference again was row()-1. This should be row()-3 for the previous row.
</div>


<div>
Thank you for your replies and by way of explanation here is the solution I was looking for.<br>Can you improve it by shortening the formula at A20?<br><br><a href="https://filedb.experts-exchange.com/incoming/2020/04_w16/1450396/Cell-up-right-is-nowok.png" rel="ugc"><br><img src="https://filedb.experts-exchange.com/incoming/2020/04_w16/1450396/Cell-up-right-is-nowok.png" class="fr-fic fr-dib"></a><br>Example xlsx is attached.&nbsp;<a href="https://filedb.experts-exchange.com/incoming/2020/04_w16/1450397/Cell-right_one_up-one.xlsx" rel="ugc">Cell=right_one_up-one.xlsx</a><br><br>Note:<br>Of course regular entries would simply be in A20: =+B19. Column A does not require a total.<br>and the running total in say B19 itself would be =+B18+A19 all the way down relative.<br>However, this is a structured table I am trying to create, which is proving to be a very slow process.<br>Thank you for having a look.<br>Anthony
</div>


Cell-up-right-is-nowok.png

Cell-right_one_up-one.xlsx

<div>
p.s. here is the context:<br><a href="https://filedb.experts-exchange.com/incoming/2020/04_w16/1450402/Period_Ends.png" rel="ugc"><img src="https://filedb.experts-exchange.com/incoming/2020/04_w16/1450402/Period_Ends.png" class="fr-fic fr-dib"></a>
</div>


Period_Ends.png

<div>
Hi, Anthony,<br><br>You are changing the question every time a solution is proposed. You even replaced the file with a new type. Please remember that people are spending time and effort on your question and if you simply rub it off the board for a new question then it gets frustrating.<br><br>If you have a new question then open a new question.<br><br>If your original question is answered then accept it else explain what is missing.
</div>


<div>
Hi Saqib, sorry if you feel messed about. As I said in my opening question, I was struggling with how to express it and invited questions for clarification.&nbsp;<br><br>I have not changed the question at all. That said I did say there was a mistake in my example file which lead to Louis providing a solution I did not need and is not relevant, and he commented he did not understand the structured formula he found there (it was summing the entire table and was basically an example of "autocorrect" being incorrect.). <br><br>As regards your own kind contribution, you started by saying you are not familiar with structured formulas in tables and it is a structured table formula I required for a solution. <br><br>I am well aware of the time people spend on these matters, being a question answerer myself, though your answers do not appear to have been excessively time consuming and you replied without recognition of my exchange with Louis (perhaps it crossed with yours?). You also sought to identify problems in my example file(s) rather than answering my question or asking for clarification given you were offering non structured solutions to my structured solution request.<br><br>I can see that what you thought was the question seemed to change as I offered clarification, which it would have been better to ask for since the solutions offered bore no relation to the structure of the file or the question or indeed structured formulae.<br><br>You are of course at liberty to request admin intervention should you feel the desire and I will comply with whatever that may decide.<br>I would like to award points even if simply to recognise effort and interest, but it seems to me I cannot do that when the solutions offered are not solutions to my problem as this would not serve the ethos of experts-exchange.<br><br>I regret this exchange having only just returned here after a long time away.<br><br>Regards Anthony<br><br><br>
</div>


<div>
Without going into discussion on your response,<br><br>The question was<br><br><h2>Excel 365 Table Structured formula <strong>wrong results</strong> and <strong>keep changing</strong></h2><br>If that is taken care of then you can close the question.<br>If something is still persistent with respect to <strong>wrong results</strong> or <strong>keep changing</strong> then we can carry on.<br><br>
</div>


<div>
While browsing through your last sheet, I note that you have included the summation <strong>within</strong> the table. Tables are limited to the data part. Header row and total row are separate from the "table" and can be switched on as an option.<br><br>So What you need to do is resize your table from $A$2:$B$20 to $A$2:$B$19.<br>Now your row 20 is <strong>outside</strong> the table<br>Once those rows are outside the table you can use these formulas<br>A20: =INDEX(DATANEW[BALANCE],ROWS(DATANEW))<br>B20: =SUM(DATANEW[ENTERED])<br><br><br><br>
</div>


<div>
Saqib, clearly you do not understand the question, I required solution INSIDE THE TABLE, as per the example's implication. The original table itself is often 100 columns wide and hundreds of rows deep, as I have said before, I posted merely an example trying to illustrate my problem. Let us leave it there please. I understand you think you understand. I have apologized for my difficulty in explaining and your additional efforts while well-intentioned are neither helping nor educating me. frankly just upsetting me when I needed some peace. Anthony
</div>


<div>
<div class="content wysiwyg-content">
(NOT pivot tables, nor data variable tables.)<br />
<br />
Excel Table Structured formulae - finding results are incorrect (i.e wrong). Found switching &quot;on&quot; iteration x2 helped. <br />
Now trying to make a cell entry, using a structured formula approach not standard, = the value of the cell one row above and one column to the right. Results vary and only occasionally are correct. &nbsp;<br />
<br />
Does anyone know of this as an issue or have a solution? <br />
<br />
MAC Excel Office 365 latest versions as at 17 April 2020<br />
&nbsp; System Version: &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;macOS 10.15.4 (19E287)<br />
<br />
<a href="https://filedb.experts-exchange.com/incoming/2020/04_w16/1450321/365version.png" target="_blank" title="Office 365 Version (12 KB)"><img alt="Office 365 Version" class="file-block lazyload" style="max-width: 240px;" data-src="https://filedb.experts-exchange.com/incoming/2020/04_w16/1450321/365version.png" /></a> <br />
&nbsp;File goes here - I am changing it because it has the wrong example formula in it.<br />
<br />
When I saved this file, the above total was reading 570,394.15, except every time I type this text and hit enter, the total increases. Iterations in prefs?<br />
&nbsp;<br />
and same issue of wrong results here:<br />
<a href="https://filedb.experts-exchange.com/incoming/2020/04_w16/1450326/Cell-up-right-is-wrong.png" target="_blank" title="Image of attached file wrong results (172 KB)"><img alt="Image of attached file wrong results" class="file-block lazyload" style="max-width: 706px;" data-src="https://filedb.experts-exchange.com/incoming/2020/04_w16/1450326/Cell-up-right-is-wrong.png" /></a> <br />
<br />
<br />
Please do not hesitate to ask for clarification as I know I am struggling for clarity with this.<br />
<br />
Long time no see, hope everyone here is well.<br />
<br />
Regards<br />
<br />
Anthony
</div>
</div>


Cell-up-right-is-wrong.png

365version.png

(NOT pivot tables, nor data variable tables.)

Excel Table Structured formulae - finding results are incorrect (i.e wrong). Found switching "on" iteration x2 helped. 
Now trying to make a cell entry, using a structured formula approach not standard, = the value of the cell one row above and one column to the right. Results vary and only occasionally are correct.  

Does anyone know of this as an issue or have a solution? 

MAC Excel Office 365 latest versions as at 17 April 2020
  System Version:	macOS 10.15.4 (19E287)


 
 File goes here - I am changing it because it has the wrong example formula in it.

When I saved this file, the above total was reading 570,394.15, except every time I type this text and hit enter, the total increases. Iterations in prefs?
 
and same issue of wrong results here:

 


Please do not hesitate to ask for clarification as I know I am struggling for clarity with this.

Long time no see, hope everyone here is well.

Regards

Anthony

Excel 365 Table Structured formula wrong results and keep changing

Microsoft Office is an integrated suite of applications that includes Outlook, Word, Excel, Access, PowerPoint, Visio and InfoPath, along with a number of tools to assist in making the individual components work together. Coding within and between the projects is done in Visual Basic for Applications, known as VBA.

Microsoft Office

Office 365 is a group of software plus services subscriptions that provides productivity software and related services to its subscribers. Office 365 allows the use of Microsoft Office apps on Windows and OS X, provides storage space on Microsoft's cloud storage service OneDrive, and grants 60 Skype minutes per month. Office 365 includes e-mail and social networking services through hosted versions of Exchange Server, Skype for Business Server, SharePoint and Office Online, integration with Yammer, as well as access to the Office software. All of Office 365's components can be managed and configured through an online portal; users can be added manually, imported from a CSV file, or Office 365 can be set up for single sign-on with a local Active Directory using Active Directory Federation Services.

Microsoft 365

Excel Table

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.