Forum Discussion
Converting a large spreadsheet to a table and formula results change
I have a large spreadsheet of data and I have converted it to a table (re-engineering old file layouts). When I do the conversion, the results of existing formulas change and I'm stumped as to why. The formulas themselves are simple
=IF(AND(AG$3>=$AD5, AG$3<=$AE5), 1, 0)
where:
AG3 is a numeric column heading (week number from 1 to 52)
AD is a WeekBegin calculation that is not affected by the conversion to a table
AE is a WeekEnd calculation also not affected by the conversion to a table
Any help would be greatly appreciated !
If you converted the range to the table and in row 3 are headers of the table, with conversion to table they were converted to the texts. In formula you shall convert them back to number with double dash or VALUE(). And better to use structured references here.
13 Replies
If you converted the range to the table and in row 3 are headers of the table, with conversion to table they were converted to the texts. In formula you shall convert them back to number with double dash or VALUE(). And better to use structured references here.
SergeiBaklan Hi Sergei, I have quite a similar issue with my table. I have attached the version of my table BEFORE converting to table. Once you convert to table you can see the figures in the green marked columns skyrocket into totally wrong results.
WIll be great to get your take on this.
Thanks.
KB
That's since range has headers as dates (e.g. 2019-01-01) formatted as text mmm yyyy. With converting range to tables headers are converted to texts, e.g. for above it'll be "Jan 2019". Within the range you have long formulas which operate with headers assuming they have dates, e.g. ...AND($E6<L$4,... L4 now is text, any text is "greater" than any number (date is actually the number), thus formulas return wrong results.
Thus within the formulas texts in headers are to be converted back to numbers (aka dates). Most probably ...AND($E6<(L$4+0),... shall work, depends on default date formats in your system.
Thank you SergeiBaklan - that worked perfectly !!!
Jo1963 , you are welcome, glad to help
Could you upload either the actual file or a sample of it (a subsection, perhaps)? In the absence of that, anything most of us could offer would be guesses, maybe educated guesses, but guesses.
It's possible, I'd think, that one of those "not affected" columns is, in fact, affected. It's also possible, since your cell references are neither altogether relative nor altogether absolute, that something in the conversion did affect those references. But those are just hypotheses, in the absence of seeing the evidence..
