Max Formula Issue... At least I think it is a max formula issue?

[nkolbaba]

Here is what I am trying to do. I have placed the values in column A into ranges like 0-1099, 1100-1499, 1500-1999, etc. So the question is how do I find the max price of the range 3000-3500. The answer should be 249900.

A B

1 SQFT Price

2 3242 242000

3 2100 146500

4 2664 465900

5 3395 249900

6 3188 164900

7 3136 240000

8 4400 259900


Thanks for your help.

 

[keymaster]

Hi there,


Welcome to Chandoo.org forums. Thanks for your question.


Assuming SQFT is in column A (A2: A9) & Price is in B2:B9, you can use the formula,


=MAX(IF(AND(A2:A9>=3000,A2:A9<=3500),B2:B9, 0)) and press CTRL+SHIFT+Enter.

This will give you maximum value for the range 3000,3500

 

[nkolbaba]

When I do that I get a 0 answer

 

[Hui]

Did you enter the formula and press Ctrl Shift Enter ?

 

[nkolbaba]

Yeah I did and still it shows a 0.

 

[Hui]

Your right


It should be:

=MAX(IF((A2:A8>=3000)*(A2:A8<=3500),B2:B8, 0))

If you want to know more about how this works have a read of : http://chandoo.org/wp/2012/01/24/formula-forensics-no-008/

 

[nkolbaba]

It worked thanks. What does "*" mean?

 

[Hui]

Nkolbaba


The * means multiply


Did you read the post by following the link above?

 

[nkolbaba]

I did read it thank you. Now I assume that if I wanted to find the minimum from the same column the formula would be

[=MIN(IF((Sheet1!$B$4:$B$100>=1099)*(Sheet1!$B$4:$B$100<=0),Sheet1!$C$4:$C$100,0))} but it does not work. Is there another trick I do not know?

 

[SirJB7]

Hi, nkolbaba!

Try changing the last part from ",0" to ",1E9", if no value greater than 99.999.999.

Regards!

 

[nkolbaba]

I am not sure if I did it right. Here is the formula {=MIN(IF((Sheet1!$B$4:$B$100>=1099)*(Sheet1!$B$4:$B$100<=0),Sheet1!$C$4:$C$100,1000000000))}. When I enter the 1E9 it becomes 1000000000 and the answer is 1000000000 in the cell. Sorry I am an idiot.

 

[SirJB7]

Hi, nkolbaba!

Don't say such a thing. You just don't know how to do this.

The correction to the formula is right, did you entered it with Ctrl-Shift-Enter?

I didn't check the formula, as it difers from the one of first topic.

If it doesn't work, please consider uploading a sample file.

Regards!

 

[Hui]

If you want the minimum value from Column B where Column A >=3000 <=3500

=MIN(IF((A2:A8>=3000)*(A2:A8<=3500),B2:B8, 9.9E+100))

Ctrl+Shift+Enter


Does it for me


Don't worry that the E+100 changes to something else

E+100 means *10 raised to the power of 100 (Just a very big number)