multiple condition min

Theadish

New Member
Joined
Jan 21, 2005
Messages
34
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.
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Theadish

New Member
Joined
Jan 21, 2005
Messages
34
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

Well-known Member
Joined
Jul 17, 2004
Messages
2,838
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

New Member
Joined
Jan 21, 2005
Messages
34
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.
 

Forum statistics

Threads
1,148,055
Messages
5,744,544
Members
423,882
Latest member
Seeham

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