Excel 365 Table Structured formula wrong results and keep changing

Hi to funny stuff in your formula define for cell E45 is "#ALL"

=SUM(DATANEW[[#All];[TSB]]), I transformed the formula into this one =SUM([TSB])

and the behaviour now is fine to me. Do I have mist some thing ?

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)
I will edit my opening question.
(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.
I will change my example file shortly.
Anthony

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.  

=row() is returning the row number where formula is define

if in A5 formula is =row() cell value will be '5'

=INDEX(A1:A5,2) // returns Value of cell A2

Select allOpen in new window

 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.

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.

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)

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.
Change the formula to  =-SUM(INDEX([HSBC],1):OFFSET([@HSBC],-1,0)) 

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.

Thank you for your replies and by way of explanation here is the solution I was looking for.
Can you improve it by shortening the formula at A20?



Example xlsx is attached. Cell=right_one_up-one.xlsx

Note:
Of course regular entries would simply be in A20: =+B19. Column A does not require a total.
and the running total in say B19 itself would be =+B18+A19 all the way down relative.
However, this is a structured table I am trying to create, which is proving to be a very slow process.
Thank you for having a look.
Anthony

p.s. here is the context:

Hi, Anthony,

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.

If you have a new question then open a new question.

If your original question is answered then accept it else explain what is missing.

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. 

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.).

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.

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.

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.

You are of course at liberty to request admin intervention should you feel the desire and I will comply with whatever that may decide.
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.

I regret this exchange having only just returned here after a long time away.

Regards Anthony

Without going into discussion on your response,

The question was

Excel 365 Table Structured formula wrong results and keep changing

If that is taken care of then you can close the question.
If something is still persistent with respect to wrong results or keep changing then we can carry on.

While browsing through your last sheet, I note that you have included the summation within 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.

So What you need to do is resize your table from $A$2:$B$20 to $A$2:$B$19.
Now your row 20 is outside the table
Once those rows are outside the table you can use these formulas
A20: =INDEX(DATANEW[BALANCE],ROWS(DATANEW))
B20: =SUM(DATANEW[ENTERED])

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