Forum Discussion

a7024782's avatar
a7024782
Brass Contributor
Oct 17, 2021
Solved

Fecha maxima con condiciones

Por favor, necesito ayuda Necesito una formula para resolver: Tengo el rango A2: B15, en la columna A registro fechas, en la columna B las variables X, Y y Z. Por favor, necesito ayuda Necesito u...
excel
Formulas and Functions
  • OliverScheurich's avatar
    OliverScheurich
    Oct 17, 2021

    a7024782 

    Hola, en su hoja de cálculo de Excel, las celdas D3, D4 y D5 tienen las letras X, Y y Z.

     

    Acabo de darme cuenta de que las fórmulas no dan el resultado correcto. Desafortunadamente, no puedo mostrarle una solución para Excel 2016.

OliverScheurich's avatar
OliverScheurich
Gold Contributor
Oct 17, 2021

a7024782 

In E3 i entered formula below and copied down to E5:

=COUNTIF($B$3:$B$15,D3)

 

I chose cell D7 to display result of Y>Z and entered formula:

=IF(AND(E4>E5,XLOOKUP($D$4,$B$3:$B$15,$A$3:$A$15,,0,1)>XLOOKUP($D$5,$B$3:$B$15,$A$3:$A$15,,0,-1)),E4,"N")

 

I chose cell D8 to display result of X>Y and entered formula:

=IF(AND(E3>E4,XLOOKUP($D$3,$B$3:$B$15,$A$3:$A$15,,0,1)>XLOOKUP($D$4,$B$3:$B$15,$A$3:$A$15,,0,-1)),E3,"N")

a7024782's avatar
a7024782
Brass Contributor
Oct 17, 2021
Thanks for the answer, unfortunately I cannot test the formula, since my Excel is version 2016 and does not have the XLOOKUP function available, is there any possibility of making it work without those functions, or with which they can be replaced in Excel 2016?
  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Oct 17, 2021

    a7024782 

    Sorry that i missed you work with Excel 2016. I work with Excel 2013 and i can use formula below (however when i am online in my Microsoft account i can work with many of the new excel functions in addition):

     

    =IF(AND(COUNTIF($B$2:$B$15;D3)>COUNTIF($B$2:$B$15;D4);INDEX($A$1:$A$15;MIN(IF($B$2:$B$15=D3;ROW($A$2:$A$15))))>INDEX($A$1:$A$15;MIN(IF($B$2:$B$15=D4;ROW($A$2:$A$15)))));D3;IF(AND(COUNTIF($B$2:$B$15;D4)>COUNTIF($B$2:$B$15;D5);INDEX($A$1:$A$15;MIN(IF($B$2:$B$15=D4;ROW($A$2:$A$15))))>INDEX($A$1:$A$15;MIN(IF($B$2:$B$15=D5;ROW($A$2:$A$15)))));D4;"N"))

     

    I have to enter formula as matrix with ctrl+shift+enter. Maybe you have to replace " ; " by " ," depending on your version of excel.

     

    Formula returns 1 result. If X>Y and Y>Z are both true, X is returned. 

     

    I can also use formula below:

     

    =IF(AND(COUNTIF(B2:B15;D4)>COUNTIF(B2:B15;D5);VLOOKUP(D4;CHOOSE({1.2};B2:B15;A2:A15);2;0)>VLOOKUP(D5;CHOOSE({1.2};B2:B15;A2:A15);2;0));D4;IF(AND(COUNTIF(B2:B15;D3)>COUNTIF(B2:B15;D4);VLOOKUP(D3;CHOOSE({1.2};B2:B15;A2:A15);2;0)>VLOOKUP(D4;CHOOSE({1.2};B2:B15;A2:A15);2;0));D3;""))

    • a7024782's avatar
      a7024782
      Brass Contributor
      Oct 17, 2021
      Muchas gracias. No puedo descargar el pdf adjunto. Tampoco tengo claro a que se refiere con D3, D4 y D5 en COUNTIF($B$2:$B$15;D3 y otras expresiones. Si no le molesta, podría reenviar el pdf?
      • OliverScheurich's avatar
        OliverScheurich
        Gold Contributor
        Oct 17, 2021

        a7024782 

        Hola, en su hoja de cálculo de Excel, las celdas D3, D4 y D5 tienen las letras X, Y y Z.

         

        Acabo de darme cuenta de que las fórmulas no dan el resultado correcto. Desafortunadamente, no puedo mostrarle una solución para Excel 2016.

Resources