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

fitzchivalry

New Member
Joined
Mar 23, 2015
Messages
33
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?
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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

New Member
Joined
Mar 23, 2015
Messages
33
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/2015ERPA.NN£10
11/01/2015BGR.GJ-£5
12/01/2015DDHEPP.GJ.HY£50
27/01/2015ERPA.NN-£40
12/02/2015DDHEPP.GJ.HY-£5
28/02/2015BGR.GJ£30
04/03/2015BGR.GJ-£30
13/03/2015ERPA.NN£20
29/03/2015DDHEPP.GJ.HY-£10
14/04/2015DDHEPP.GJ.HY£5

<tbody>
</tbody>

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

Product IDMost 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 IDMost Recent Sale
DDHEPP.GJ.HY29/03/2015
BGR.GJ04/03/2015
ERPA.NN27/01/2015

<tbody>
</tbody>

Thanks!

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

fitzchivalry

New Member
Joined
Mar 23, 2015
Messages
33

ADVERTISEMENT

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

MrExcel MVP
Joined
May 7, 2008
Messages
21,779
Office Version
  1. 2010
Platform
  1. Windows
Try

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

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,779
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

Or

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

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
Row\Col
A​
G​
K​
L​
M​
N​
O​
1​
DateProduct IDTransaction AmountProduct IDMost 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:
Rich (BB code):

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

fitzchivalry

New Member
Joined
Mar 23, 2015
Messages
33
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

New Member
Joined
Mar 23, 2015
Messages
33
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:
Rich (BB code):

=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.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,577
Messages
5,596,996
Members
414,116
Latest member
sfullnet

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top