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 08, 2018
        nice formula....
        congratz
  • 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
    • Man Fai Chan's avatar
      Man Fai Chan
      Iron Contributor
      Nov 07, 2018

      The problem can be solved if there is a function "RANKIF". I found it very complicated, especially no additional columns is going to add. You may refer to the following page:

       

      https://contexturesblog.com/archives/2017/03/09/excel-rank-formula-example/

       

      Once you developed the "RANKIF" function, you can simply use an "IF" to give the value 0 or other values. 

       

      • Lorenzo Kim's avatar
        Lorenzo Kim
        Bronze Contributor
        Nov 07, 2018
        Mr. Chan
        Thank you for the info...
  • Lorenzo Kim's avatar
    Lorenzo Kim
    Bronze Contributor
    Nov 06, 2018
    try sorting the date then-
    change the first cell (A2) to the cell of the first day of the next month and (A999) to the last day of that month and so on...

Resources