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?

 

[Aladin Akyurek]

[...]
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.

The exhibit shows

DDHEPP.GJ.HY

in N2 and other values downwards.

The formula must go in O2 (not in D2)...

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

which must be confirmed with control+shift+enter and copied down.

 

[fitzchivalry]

I tried again, I'd made an error at this end the first time - there was nothing wrong with it, that solution works too now .

The error I made was naming the wrong range where the formula says $G$2:$G$11; I was comparing against Customer ID's instead of Product ID's.

Also, it works for me in P2 as well as O2.

Also, I wrote this by myself today (with the help of a guide on COUNTIFS):
=COUNTIFS(TransConfirmedTrans,"<0",TransProduct,$A2)
It counts Sales excluding Refunds for a particular identifier.
Am quite pleased with myself (I still consider a novice Excel user).

The "<0" part in there I learnt from this thread .

 

[vp3681]

Hi,
You can achieve same result by using AGGREGATE Funciton.
=AGGREGATE(14,6,$A$2:$A$11/(($B$2:$B$11=E2)*($C$2:$C$11<0)),1)

Column A Column B Column C Column E Column F

Date (A)	Product ID (G)	Transaction Amount (K)		Product ID	Most Recent Sale
02-01-2015	ERPA.NN	£10.00		DDHEPP.GJ.HY	29-03-2015
11-01-2015	BGR.GJ	-£5.00		BGR.GJ	04-03-2015
12-01-2015	DDHEPP.GJ.HY	£50.00		ERPA.NN	27-01-2015
27-01-2015	ERPA.NN	-£40.00
12-02-2015	DDHEPP.GJ.HY	-£5.00
28-02-2015	BGR.GJ	£30.00
04-03-2015	BGR.GJ	-£30.00
13-03-2015	ERPA.NN	£20.00
29-03-2015	DDHEPP.GJ.HY	-£10.00
14-04-2015	DDHEPP.GJ.HY	£5.00

<tbody>
</tbody>

By using aggregate, you can avoid to use Crtl+Shift+Enter (Array).

 

[Aladin Akyurek]

[...]

By using aggregate, you can avoid to use Crtl+Shift+Enter (Array).

Is this function faster than the equivalent you are trying to replace? What is the flexilibity score we get in trying to avoid control+shift+enter?

 

[vp3681]

Hi,
Yes AGGREGATE is faster.
It is flexible as it is very easy to understand and execute. It is an single in-built function.
You must be agree with me that CSE formulas are using multiple function (like MAX, IF, LOOKUP etc) which makes them difficult to understan by normal user.

 

[Aladin Akyurek]

Hi,
Yes AGGREGATE is faster.
It is flexible as it is very easy to understand and execute. It is an single in-built function.
You must be agree with me that CSE formulas are using multiple function (like MAX, IF, LOOKUP etc) which makes them difficult to understan by normal user.

I don't think it's faster. It is not a single function since it runs other functions (with numeric mnemonics). Every user extends his/her repertoire with growing needs. Taking a stance against array-processing formulas requiring CSE precludes a large class of problems that requires them and most cannot be solved with AGGREGATE,