Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
3 years ago

How to write this excel formula?

This formula gives me the year of my cooler depending on the serial number. The variants are: Length of the series, with which numbers it begins, etc. I hope you can help me

=IF(BLANK(B2),"",SI. ERROR(SI(B2="S/E","S/E",SI(Y(LEFT(TEXT(B2,1)),LONG(B2)=12,EXTRACT(B2,4,2)*1=22),2022,IF(Y(LENGTH(B2)=13,LEFT(B2,2)*1=15),2015,IF(Y(LENGTH(B2)=12,LEFT(B2,2)*1=15),2015,IF(Y(LONG(B2)=9,LEFT(B2,2)*1=15),2015,IF(Y(LENGTH(B2)=13, LEFT(B2,2)*1=16),2016,SI(Y(LONG(B2)=12,LEFT(B2,2)*1=16),2016,IF(Y(LONG(B2)=13,LEFT(B2,2)*1=17),2017,IF(Y(LONG(B2)=12,LEFT(B2,2)*1=17),2017,IF(Y(OR(LONG(B2)=14,LONG(B2)=15),LEFT(B2,3)*1=617),2017,IF(Y(LENGTH(B2)=9, EXTRACT(B2,2,2)*1=17),2017,IF(Y(O(LONG(B2)=12,LONG(B2)=13),LEFT(B2,2)*1=18),2018,IF(Y(O(LENGTH(B2)=11,LENGTH(B2)=12,LONG(B2)=13),LEFT(B2,2)*1=19),2019,IF(Y(LENGTH(B2)=13,LEFT(B2,2)*1=20),2020,IF(Y(LENGTH(B2)=13,LEFT(B2, 2)*1=21),2021,SI(Y(LONG(B2)=13,LEFT(B2,2)*1=22),2022,IF(Y(LONG(B2)=13,LEFT(B2,2)*1=23),2023,"<2015")),"<2015"))

2 Replies

    • Syndicate_Admin's avatar
      Syndicate_Admin
      Administrator
      3 years ago

      this is how I put it in DAX but it gives me an error:
      Expressions that produce Variant data types cannot be used to define calculated columns.

      AÑO_EMFRIADOR =
      IF(ISBLANK([SERIES]),"",
      IFERROR(
      IF([SERIES]="S/E","S/E"
      ,IF(AND(ISTEXT(LEFT([SERIES],1)),AND(ONLY([SERIES])*1=12,MID([SERIES],4,2)*1=22)),2022
      ,IF(AND(ONLY([SERIES])*1=13,LEFT([SERIES],2)*1=15),2015
      ,IF(AND(ONLY([SERIES])*1=12,LEFT([SERIES],2)*1=15),2015
      ,IF(AND(ONLY([SERIES])*1=9,LEFT([SERIES],2)*1=15),2015
      ,IF(AND(ONLY([SERIES])*1=13,LEFT([SERIES],2)*1=16),2016
      ,IF(AND(ONLY([SERIES])*1=12,LEFT([SERIES],2)*1=16),2016
      ,IF(AND(ONLY([SERIES])*1=13,LEFT([SERIES],2)*1=17),2017
      ,IF(AND(ONLY([SERIES])*1=12,LEFT([SERIES],2)*1=17),2017
      ,IF(AND(OR(ONLY([SERIES])*1=14,ONLY([SERIES])*1=15),LEFT([SERIES],3)*1=17),2017
      ,IF(AND(ONLY([SERIES])*1=9,MID([SERIES],2,2)*1=17),2017
      ,IF(AND(OR(ONLY([SERIES])*1=12,ONLY([SERIES])*1=13),LEFT([SERIES],2)*1=18),2018
      ,IF(AND(OR(ONLY([SERIES])*1=11,OR(ONLY([SERIES])*1=12,ONLY([SERIES])*1=13)),LEFT([SERIES],2)*1=19),2019
      ,IF(AND(ONLY([SERIES])*1=13,LEFT([SERIES],2)*1=20),2020
      ,IF(AND(ONLY([SERIES])*1=13,LEFT([SERIES],2)*1=21),2021
      ,IF(AND(ONLY([SERIES])*1=13,LEFT([SERIES],2)*1=22),2022
      ,IF(AND(ONLY([SERIES])*1=13,LEFT([SERIES],2)*1=23),2023,">2015")))))))))))))))))
      ,<2015))
      The only thing you should return is a column with a year of coolers. 2022,2021,2020 etc.
      The series of my coolers have multiple variants that define the year of the cooler: Length of the series, if it starts with letter, if it starts with 22, if the 3rd digit is a year. All these variants are considered in the DAX that I put as an example. I wish they could help me. If they occupy more information let me know