Trying to find date of most recent negative transaction for a particular identifier...(MAXIFS?)

[fitzchivalry]

Hi

I have a spreadsheet with Sales Transaction Data on one sheet:
with dates in A (TransDate),
product identifiers in G (TransProduct),
transaction amounts in K (TransConfirmedtransactions),

and Product Stats on another sheet:
with Product Identifiers in A
and the formula going in E2 for example.

In E2 on the Product Stats page I'd like to display the date of the most recent negative transaction.

So:
Looking for Product Identifier; found in $A2 on the sheet the formula is being entered on.
Looking for this in range TransProduct.
Then checking if the amount for the corresponding transaction is positive, and if so, moving on to the next most recent match until a negative is found.
Then displaying the date of this in the cell.

I have no idea if this is actually possible, but it seems Excel can do just about anything...

-\/- My efforts at solving this, unfruitful so far -\/-

I have an array formula (that I found on this forum I'm pretty sure) {=MAX(IF(TransProduct=$A2,TransDate,""))} so am using that as a starting point.

And I've seen =COUNTIF($A$1:$A$8,"<0") (here http://www.mrexcel.com/forum/excel-questions/31251-count-negative-cells-count-positive-cells.html) which counts negative amounts, and I take from that "<0" will probably be part of the answer.

MAX IFs:

I was reading on another about creating a MAX formula with multiple IFs. This sounds like what I need.

I've tried

{=MAX(IF(TransCust=$A2,IF(TransConfirmedTrans=<0,TransDate,"")))}

I get corrected by Excel when I do that, so I've counted all the various brackets here:

{=MAX
(IF
(TransCust=$A2,
IF(TransConfirmedTrans<=0,TransDate,""
)
)
)
}

Now I'm stumped, am still working on this but maybe someone can tell me if I'm on the right track?

 

[fitzchivalry]

I did find that MAX IF here, here: http://www.mrexcel.com/forum/excel-questions/253103-return-only-last-transaction-date.html

And blog post about MAX IFS here: http://blog.contextures.com/archives/2013/11/19/using-max-if-with-multiple-criteria/

 

[Aladin Akyurek]

If not solved, would you please post a small sample (input) and the expected results? A concise description (without resort to any formula) would enhance understanding...

 

[fitzchivalry]

Hi

Thank you for reading and replying

This is sample input data, with irrelevant columns omitted.

Date (A)	Product ID (G)	Transaction Amount (K)
02/01/2015	ERPA.NN	£10
11/01/2015	BGR.GJ	-£5
12/01/2015	DDHEPP.GJ.HY	£50
27/01/2015	ERPA.NN	-£40
12/02/2015	DDHEPP.GJ.HY	-£5
28/02/2015	BGR.GJ	£30
04/03/2015	BGR.GJ	-£30
13/03/2015	ERPA.NN	£20
29/03/2015	DDHEPP.GJ.HY	-£10
14/04/2015	DDHEPP.GJ.HY	£5

<tbody>
</tbody>

And this is the Product Performance sheet, with columns omitted.

Product ID	Most Recent Sale
DDHEPP.GJ.HY	!!!
BGR.GJ
ERPA.NN

<tbody>
</tbody>

In the cell I've entered !!! in, I'd like to show the date (from the first column of the first sheet) of the most recent negative transaction for the Product ID, matched from $A2 of the second sheet here against Product ID (column $G on my sheet, B of the first sheet here).

So in this case I'd expect to see the above table all filled out showing:

Product ID	Most Recent Sale
DDHEPP.GJ.HY	29/03/2015
BGR.GJ	04/03/2015
ERPA.NN	27/01/2015

<tbody>
</tbody>

Thanks!

I'm reading about nested IF formula's, I think the answer is here somewhere!

 

[fitzchivalry]

I'm using {=MAX(IF(TransCust=$A56,IF(TransConfirmedTrans&">0",TransDate,"")))} as an array formula. It returns #VALUE!

I don't know quite what that & is doing in there, I don't think it's helping.

 

[shg]

Try

{=MAX(IF(TransCust = $A56, IF(TransConfirmedTrans > 0, TransDate)))}

 

[shg]

Or

{=MAX(IF((TransCust = $A56) * (TransConfirmedTrans > 0), TransDate))}

 

[Aladin Akyurek]

Row\Col	A​	G​	K​	L​	M​	N​	O​
1​	Date	Product ID	Transaction Amount			Product ID	Most Recent Sale
2​	1/2/2015​	ERPA.NN	10​			DDHEPP.GJ.HY	3/29/2015​
3​	1/11/2015​	BGR.GJ	-5​			BGR.GJ	3/4/2015​
4​	1/12/2015​	DDHEPP.GJ.HY	50​			ERPA.NN	1/27/2015​
5​	1/27/2015​	ERPA.NN	-40​
6​	2/12/2015​	DDHEPP.GJ.HY	-5​
7​	2/28/2015​	BGR.GJ	30​
8​	3/4/2015​	BGR.GJ	-30​
9​	3/13/2015​	ERPA.NN	20​
10​	3/29/2015​	DDHEPP.GJ.HY	-10​
11​	4/14/2015​	DDHEPP.GJ.HY	5​
12​

D2, control+shift+enter, not just enter, and copy down:

=LOOKUP(9.99999999999999E+307,IF($G$2:$G$11=N2,IF($K$2:$K$11<0,$A$2:$A$11)))

 

[fitzchivalry]

Thank you, on first attempt with each of these, I haven't been successful.

It's late, I might have better success tomorrow. I'll respond either way, thanks very much, interesting solutions!

 

[fitzchivalry]

Or

{=MAX(IF((TransCust = $A56) * (TransConfirmedTrans > 0), TransDate))}

Thank you, I made a slight adjustment (below) and this works, amazing, I will have to learn what that * is doing to find out why and how this works.

{=MAX(IF((TransCust = $A56) * (TransConfirmedTrans < 0), TransDate))}

D2, control+shift+enter, not just enter, and copy down:

=LOOKUP(9.99999999999999E+307,IF($G$2:$G$11=N2,IF($K$2:$K$11<0,$A$2:$A$11)))

I took D2 to mean do this in the cell you're entering the formula in so I went to F2 and ctrl+shift_entered this; substuting ranges for named ranges.

=LOOKUP(9.99999999999999E+307,IF(TransCust=$A2,IF(TransConfirmedtransactions<0,TransDate)))

It returned #N/A, I dragged it down. Maybe if I tweaked this a bit in places I could get it to work but I'll use the above since it's works.

I'm not familiar with 9.99999999 in formulas, so I searched for it and found your excellent explanation http://www.mrexcel.com/forum/excel-questions/102091-9-9999999-a.html, thank you I'm reading this now.