MAX or MIN from a conditional statement

[DJo]

Thank you all for your help last time. It worked! Now I have a similar problem I wonder if you can help me with...

I'll just paste in the same spreadsheet as last time so you get the idea.

I have a spreadsheet showing the name of a product followed by how much it will sell for in each location. In another column I have a flag, either 0 or 1 to indicate that the product can be purchased from that location. The buy and sell prices are equal (no profit).

I want to find out what the lowest and then the higest sell price for each product is. Here is how my spreadsheet is layed out:


-----------
Product A Location Sells Product B Location Sells
Location 1 200 0 150 1
Location 2 100 1 300 0
Location 3 300 1 150 0

0 = No
1 = Yes

-----------


This is how I propose to find the highest or lowest sell price:

If "Location Sells" this item, add it to the array
Loop until all locations have been tested
Find the highest (or lowest) value in the array

Thanking you in advance.

Iain

P.S. I still have no idea how to use Access

 

[Dave Smith]

How about

=MIN(IF(C1:C3=1,B1:B3))

and

=MAX(IF(C1:C3=1,B1:B3))

These must be arrary entered (Ctrl+Shift+Enter).

HTH

-Dave

Thank you all for your help last time. It worked! Now I have a similar
problem I wonder if you can help me with...

I'll just paste in the same spreadsheet as last time so you get the idea.

I have a spreadsheet showing the name of a product followed by how much it
will sell for in each location. In another column I have a flag, either 0 or
1 to indicate that the product can be purchased from that location. The buy
and sell prices are equal (no profit).

I want to find out what the lowest and then the higest sell price for each
product is. Here is how my spreadsheet is layed out:


-----------
Product A Location Sells Product B
Location Sells
Location 1 200 0 150
1
Location 2 100 1 300
0
Location 3 300 1 150
0

0 = No
1 = Yes

-----------


This is how I propose to find the highest or lowest sell price:

If "Location Sells" this item, add it to the array
Loop until all locations have been tested
Find the highest (or lowest) value in the array

Thanking you in advance.

Iain

P.S. I still have no idea how to use Access

 

[Myrna Larson]

You can do this with an array formula rather than a macro.

Say product A prices are in B2:B100, flag for whether location sells it in C2:C100

=MAX(IF(C2:C100=1,B2:B100,""))

Enter this with CTRL+SHIFT+ENTER. Change MAX to MIN for the minimum.

Thank you all for your help last time. It worked! Now I have a similar problem I wonder if you can help me with...

I'll just paste in the same spreadsheet as last time so you get the idea.

I have a spreadsheet showing the name of a product followed by how much it will sell for in

each location. In another column I have a flag, either 0 or 1 to indicate that the product can
be purchased from that location. The buy and sell prices are equal (no profit).

I want to find out what the lowest and then the higest sell price for each product is. Here is

how my spreadsheet is layed out: