How to see column calculated formula? or what's the logic?

[whatever61]

Can someone explain the logic of column calculated formula?

This is very frustrating, It's just randomly does whatever it feels like.. I tried to copy-paste the new formula all over the column, but still when a new row is created in the table it takes some old formula I used some time ago.
Where is stored? how to change EXACTLY?

MS website says something I don't understand.. delete cells? what delete cells has to do with it? of course I can delete the column and recreate it, that's the most stupid solution ever proposed or I don't understand it..

Also, I remove the settings to show error when the formula is inconsistent, after restarting Excel all settings just reset to what they were.

Thanks

[gmr4evr1]

It would probably be easier if you could attach the workbook (desensitized) so we could look at it and see where the problem lies.

[FDibbins]

I can understand your frustrations, however, in order to help, it would be good to see samples of what you are working with.

What exactly if the "column formula" and what does it refer to?

[whatever61]

Guys, it's a general question. I am not sure how a sample file can help you. Either you know the logic of it or you don't

I actually confused the name. Sorry. The right name is Calculated Column. It's the formula that will be autofilled in when a new row is created in a table.

You can read more about this here: https://support.office.com/en-nz/art...b-743a2650af3c

[gmr4evr1]

Good luck to you

[FDibbins]

OK, I think I understand now.

If you are using a Table, then you should not (need to) copy formulas down...that's he whole point of using Tables in the 1st place. As soon as you start a new row, it replicates the formulas for you.

I have tried to re-create your description in a Table, but any time I change a formula (typed, pointed to or copy/pasted), it applies that to the entire column.

hmm wait. If I change a formula, it does indeed chyange the whole column, but if I undo once, it undoes the column changes, and just leave the single-cell change. I can then copy/paste that around in the column, and it does not auto-adjust. Also, any further formula changes I make are also not carried through. Is this perhaps what may have happened to you?

If you want to have different formulas within the same column, then perhaps Tables is not what you need to use?

[whatever61]

OK, I think I understand now.

hmm wait. If I change a formula, it does indeed chyange the whole column, but if I undo once, it undoes the column changes, and just leave the single-cell change. I can then copy/paste that around in the column, and it does not auto-adjust. Also, any further formula changes I make are also not carried through. Is this perhaps what may have happened to you?

If you want to have different withinthe same column, then perhaps Tables is not what you need to use?

Yes, you got this right. This is the problem I am talking about. In some cases copy-pasting the formula all over the column works. In my case some columns need to have mixed formulas and I want a specific one to be filled in always in a new row.
Table fits for me, it's just this annoying thing..

[FDibbins]

Table fits for me, it's just this annoying thing.

I get that, tables are not my favorite things to work with, I invariably end up typing inthe cell refs manually.

I guess when you mess with how it's supposed to work, it doesnt work anymore

[shg]

In my case some columns need to have mixed formulas and I want a specific one to be filled in always in a new row.

That's arguably symptomatic of a bad design, but inarguably incompatible with a table. Convert it to a range.

[FDibbins]

If you want to have different formulas within the same column, then perhaps Tables is not what you need to use?

Pretty much what I said too, shg

[whatever61]

That's arguably symptomatic of a bad design, but inarguably incompatible with a table. Convert it to a range.

Even if I have a consistent formula, it still takes the wrong one to the new row (some old formula I used before).
Where's the logic in that?

[shg]

Perhaps convert the table to a range, correct all the formulas so that they copy straight down, then convert back to a table.

[whatever61]

Obviously I can do this kind of stuff.
My question is what is the logic behind. Excel takes this formula from SOMEWHERE, right? That is what I am trying to discover..

[shg]

I have no clue. Excel has lots of retained state information, like the parameters of the last sort on each sheet, the last Solver model, custom colors, .... Some of it (like colors) is in relatively plain view in the consituents of the zip file, some may be buried in the binary file.

What would you do if you found it?

[whatever61]

Finding it is not the most important. As I mentioned.. I want to understand the logic behind it.

There is SOME logic, right? based on something it decides to put this value or another in the new row. I want to understand that logic.

[whatever61]

Ok, I found a way to overwrite the calculated formula at least.

What you have to do is take the formula you would like to put on the whole column, edit the formula and press ENTER.
You will have an option now to OVERWRITE THE WHOLE COLUMN with this formula.

This will only work in case you don't have mixed formulas/values in a single column. In that case what I did is, I copy-pasted what is already there in the column aside, then overwritten the calculated formula and then pasted it back.
Phew.