Forum Discussion

MATEO LARESGOITI's avatar
MATEO LARESGOITI
Copper Contributor
Nov 05, 2018
Solved

Excel Challenge HELP - If you want a challenge with Date, List, Filtering...

I have a list of products sold and the dates they were sold. The first 3 products sold every month are given away for free. So I need in a single cell (if possible), to calculate if the product is on...
excel
Excel on Mac
Formulas and Functions
  • Willy Lau's avatar
    Willy Lau
    Nov 08, 2018
    =IF(OR(SUMPRODUCT(TEXT($B2,"yyyymm")=TEXT($B$1:$B$51,"yyyymm"))*($B2>$B$2:$B$51))+1)={1,2,3}),0,$C2)

    $B$2:$B$51 is supposed for the sold date with time.

     

    reference : excel rank by group

Lorenzo Kim's avatar
Lorenzo Kim
Bronze Contributor
Nov 06, 2018

try:
Column A is the "DATE", Column B is the "PRODUCT" and Column C is the "VALUE"
add Column D as "ADJUSTED VALUE"
in Cell D2 - paste formula below and copy down to suit:
=IFERROR(IF(OR(SMALL($A$2:$A$999,1)=A2,SMALL($A$2:$A$999,2)=A2,SMALL($A$2:$A$999,3)=A2),"",C2),"")

where cell A2 is the start of the month
and cell A999 is the end of the month

HTH

  • MATEO LARESGOITI's avatar
    MATEO LARESGOITI
    Copper Contributor
    Nov 06, 2018

    Thanks for your reply but it still doesn't seem to do the trick. It's working but it gives me only the first three products within all the list, not the first three products of every month. Here is a screen-shot: 

    • Willy Lau's avatar
      Willy Lau
      Iron Contributor
      Nov 08, 2018
      =IF(OR(SUMPRODUCT(TEXT($B2,"yyyymm")=TEXT($B$1:$B$51,"yyyymm"))*($B2>$B$2:$B$51))+1)={1,2,3}),0,$C2)

      $B$2:$B$51 is supposed for the sold date with time.

       

      reference : excel rank by group

      • MATEO LARESGOITI's avatar
        MATEO LARESGOITI
        Copper Contributor
        Nov 08, 2018
        Perfect! Thank you it worked perfectly! I actually altered it a bit and ended up with something a little simpler:

        =IF((SUMPRODUCT((TEXT($D$6:$D$999,"yyyymm")=TEXT($D6,"yyyymm"))*(D6>$D$6:$D$999))+1)<=3,0,$C2)
    • Lorenzo Kim's avatar
      Lorenzo Kim
      Bronze Contributor
      Nov 07, 2018

      I have a new approach.

      You must save the file as Macro Enabled (.xlsm)

      pls see attached file.

      the column for DATE will be sorted and the top 3 value changed to zero.

      press ALT F8 then run the Macro

      hope this can help.

      thanks..

      • MATEO LARESGOITI's avatar
        MATEO LARESGOITI
        Copper Contributor
        Nov 08, 2018
        Thank you! It worked perfectly

Resources