Forum Discussion
MATEO LARESGOITI
Nov 05, 2018Copper Contributor
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 one of the first 3 and return a value of 0 if true.
Im trying to do this within one cell (I'm trying to avoid additional lists)
*Keep in mind there are many months and years
=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 KimBronze Contributor
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 LARESGOITICopper Contributor
- Willy LauSteel Contributor
=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