Forum Discussion

Jo1963's avatar
Jo1963
Copper Contributor
Mar 18, 2020
Solved

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. ...
BI & Data Analysis
excel
Formulas and Functions
office 365
  • SergeiBaklan's avatar
    SergeiBaklan
    Mar 18, 2020

    Jo1963 

    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's avatar
SergeiBaklan
Diamond Contributor
Mar 18, 2020

Jo1963 

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.

NoviceKB's avatar
NoviceKB
Brass Contributor
May 23, 2020

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

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    May 23, 2020

    NoviceKB 

    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.

    • NoviceKB's avatar
      NoviceKB
      Brass Contributor
      May 23, 2020

      SergeiBaklan Thanks for the prompt feedback. I actually tried this but now I am still getting the wrong result. I have included a testcase from the exact sheet I will be using for the report. in column AB7 I have implemented your suggestion and copy pasted the formula across. Especially in this particular example I am expecting to see 16000 in every cell but I am getting some low and different figures still. Pls see attachment.

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        May 23, 2020

        NoviceKB 

        That was my mistake, sorry. Text as June-20, if converting to date, Excel is considered as June 20 of the current year, other words as June 20, 2020. We need here first of the month, i.e. June 01, 2020.

        If we consider that we work in current year only, we may use EOMONTH(AB$6,-1)+1 for the converting, entire formula will be

        =IF(
   OR($F7 > EOMONTH(AB$6,0), AND($G7<(EOMONTH(AB$6,-1)+1),$G7>0)),
   0,
   $P7/$Q7/DAY(EOMONTH(AB$6,0))*
      IF(
         AND($S7>0,$T7>0),
         MAX(0,MIN($S7-1,EOMONTH(AB$6,0))-MAX($F7,(EOMONTH(AB$6,-1)+1))+1)+
         MAX(0,
            IF($G7>0,
               MIN($G7,EOMONTH(AB$6,0)),
               EOMONTH(AB$6,0)
            ) -
            MAX(EOMONTH(AB$6,-1)+1,$T7)+1
         ),
         MAX(0,
            IF($G7>0,
               MIN($G7,EOMONTH(AB$6,0)),
               EOMONTH(AB$6,0)
            ) -
            MAX(EOMONTH(AB$6,-1)+1,$F7)+1
         )
      )
)

         However, more reliable will be converting with DATE() by parsing the month-year text, i.e.

        DATE(20*100+RIGHT(AB$6,2),MONTH(1&LEFT(AB$6,LEN(AB$6)-4)),1)

        As variant, to simplify the formula you may add helper row on the top of the table headers and use it in calculation instead of header.

Resources