multiple condition min

[Theadish]

Hey guys. Here's my problem. I have a data dump from another program that I only need certain numbers from. The data dump contains time-stamped transactions that occurred during a given month. The time-stamp contains the date and the hour. Each transaction takes up around 4 rows but always 8 columns. All 3-5 or so rows of each transaction have the same time-stamp in column 2. The numbers I need are the price of the transaction, which is only listed on the last row of that transaction in column 8. The other price rows are blank. To make it even more complicated, sometimes multiple transactions can be done in one hour, but not necessarily every hour has a transaction. My final result needs to be a column going down 744 rows for each hour in a 31 day month with the minimum price of each hour's transactions.

I tried using a sum array based on the day and hour, but on hours with multiple transactions it, of course, summed the prices. I also tried the same thing but with min instead of sum, but since most of the cells are blank, it always returned 0.

Thanks for the help, and if you need more info just let me know.

 

[shades]

Can you use =MIN(IF....) ?

 

[Theadish]

Unless I'm doing it wrong somehow, no, it's not working. I used the formula: =MIN(IF(AND($D$2:$D$1864=K2,$E$2:$E$1864=L2,$G$2:$G$1864>0),$G$2:$G$1864,0))

In pseudo-code I basically said min(if(and(transaction day column = specific day, transaction hour column = specific hour, price column > 0), price column, 0)).

I tried it as an array statement and not and neither seemed to work...

 

[PA HS Teacher]

Try the following, I used Column C as the "Price Column", You can change the other ranges to meet your need.

=MIN(IF((A1:A10="B")*(B1:B10=3)*(C1:C10>0)*C1:C10>0,(A1:A10="B")*(B1:B10=3)*(C1:C10>0)*C1:C10,10000000000000000))

Array Entered.

 

[Theadish]

Aha! It worked beautifully! I had tried that formula once but put 0 instead of that really large number, so I was always getting 0's. Thanks so much. That just saved me a lot of time.