Max and min with text criteria

[SamRezound82]

Based on different text criteria, I am trying to return the Max and Min numbers for each criteria from a separate table

Example:

Criteria Table:

Unit
Type Min Rent Max Rent
0 BR ______ _______

1 BR _______ _______

2 BR _______ _______

3 BR _______ _______


Data Table:

Type Rent
0 BR 4,000
0 BR 3,500
0 BR 5,500
1 BR 6,000
1 BR 2,500
1 BR 6,200
2 BR 8,000
2 BR 4,500
2 BR 7,000
3 BR 10,000
3 BR 12,000
3 BR 11,000

 

[jeffreybrown]

How about...

These are CSE formulas

Data Range
[Table="class: grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][td]

H​

[/td][td]

I​

[/td][/tr]
[tr][td]

1​

[/td][td]

Type​

[/td][td]

Rent​

[/td][td]

​

[/td][td]

​

[/td][td]

Max​

[/td][td]

Min​

[/td][td]

​

[/td][td]

Max​

[/td][td]

E2:=MAX(IF($A$2:$A$13=$D2,$B$2:$B$13))​

[/td][/tr]

[tr][td]

2​

[/td][td]

0 BR​

[/td][td]

4,000​

[/td][td]

​

[/td][td]

0 BR​

[/td][td]

5,500​

[/td][td]

3,500​

[/td][td]

​

[/td][td]

Min​

[/td][td]

F2:=MIN(IF($A$2:$A$13=$D2,$B$2:$B$13))​

[/td][/tr]

[tr][td]

3​

[/td][td]

0 BR​

[/td][td]

3,500​

[/td][td]

​

[/td][td]

1 BR​

[/td][td]

6,200​

[/td][td]

2,500​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]

[tr][td]

4​

[/td][td]

0 BR​

[/td][td]

5,500​

[/td][td]

​

[/td][td]

2 BR​

[/td][td]

8,000​

[/td][td]

4,500​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]

[tr][td]

5​

[/td][td]

1 BR​

[/td][td]

6,000​

[/td][td]

​

[/td][td]

3 BR​

[/td][td]

12,000​

[/td][td]

10,000​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]

[tr][td]

6​

[/td][td]

1 BR​

[/td][td]

2,500​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]

[tr][td]

7​

[/td][td]

1 BR​

[/td][td]

6,200​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]

[tr][td]

8​

[/td][td]

2 BR​

[/td][td]

8,000​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]

[tr][td]

9​

[/td][td]

2 BR​

[/td][td]

4,500​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]

[tr][td]

10​

[/td][td]

2 BR​

[/td][td]

7,000​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]

[tr][td]

11​

[/td][td]

3 BR​

[/td][td]

10,000​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]

[tr][td]

12​

[/td][td]

3 BR​

[/td][td]

12,000​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]

[tr][td]

13​

[/td][td]

3 BR​

[/td][td]

11,000​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]
[/table]

 

[SamRezound82]

Thank you. It seemed CSE was the way to go.

The other problem I have is that there is one 4 BR but it is vacant where column b would have Vacant (see below). It should return 0 or - instead of an error code if possible.

A B
4 BR Vacant

 

[jeffreybrown]

It returns a zero for me. What exactly does it return for you?

E2: =MAX(IF($A$2:$A$14=$D2,$B$2:$B$14))

Data Range
[Table="class: grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][/tr]
[tr][td]

1​

[/td][td]

Type​

[/td][td]

Rent​

[/td][td]

​

[/td][td]

​

[/td][td]

Max​

[/td][td]

Min​

[/td][/tr]

[tr][td]

2​

[/td][td]

0 BR​

[/td][td]

4,000​

[/td][td]

​

[/td][td]

0 BR​

[/td][td]

5,500​

[/td][td]

3,500​

[/td][/tr]

[tr][td]

3​

[/td][td]

0 BR​

[/td][td]

3,500​

[/td][td]

​

[/td][td]

1 BR​

[/td][td]

6,200​

[/td][td]

2,500​

[/td][/tr]

[tr][td]

4​

[/td][td]

0 BR​

[/td][td]

5,500​

[/td][td]

​

[/td][td]

2 BR​

[/td][td]

8,000​

[/td][td]

4,500​

[/td][/tr]

[tr][td]

5​

[/td][td]

1 BR​

[/td][td]

6,000​

[/td][td]

​

[/td][td]

3 BR​

[/td][td]

12,000​

[/td][td]

10,000​

[/td][/tr]

[tr][td]

6​

[/td][td]

1 BR​

[/td][td]

2,500​

[/td][td]

​

[/td][td]

4 BR​

[/td][td]

0​

[/td][td]

0​

[/td][/tr]

[tr][td]

7​

[/td][td]

1 BR​

[/td][td]

6,200​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]

[tr][td]

8​

[/td][td]

2 BR​

[/td][td]

8,000​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]

[tr][td]

9​

[/td][td]

2 BR​

[/td][td]

4,500​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]

[tr][td]

10​

[/td][td]

2 BR​

[/td][td]

7,000​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]

[tr][td]

11​

[/td][td]

3 BR​

[/td][td]

10,000​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]

[tr][td]

12​

[/td][td]

3 BR​

[/td][td]

12,000​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]

[tr][td]

13​

[/td][td]

3 BR​

[/td][td]

11,000​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]

[tr][td]

14​

[/td][td]

4 BR​

[/td][td]

Vacant​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]
[/table]

 

[SamRezound82]

#N/A

 

[jeffreybrown]

Then try to wrap it with IFERROR

=IFERROR(MAX(IF($A$2:$A$14=$D2,$B$2:$B$14)),"")

 

[SamRezound82]

Worked!!

Thank you

 

[jeffreybrown]

You are very welcome.

 

[BlondieC]

Hi Jeffrey Brown

I'm reading through posts to see if I can find a solution before posting a new thread and this one looks like it may get me started in the right direction. I do have one question though - can this CSE (had to google to see what that stood for), be used on WorksheetA to find data values on multiple worksheets and place those found values back on WorksheetA in? Possibly not a Yes or No question but thank you.

Then try to wrap it with IFERROR

=IFERROR(MAX(IF($A$2:$A$14=$D2,$B$2:$B$14)),"")

 

[jeffreybrown]

Yes, an array formula (CSE) can be used to pull from different areas within a workbook.

IMPORTANT

• This is an array formula
• Enter the formula >> press F2 then >> CTRL + SHIFT + ENTER
• If entered correctly, the formula will be enclosed in {brackets}
• Do not enter the {brackets} manually

As you can see here...

=MAX(IF('Min Max'!$A$2:$A$14=$A2,'Min Max'!$B$2:$B$14))

There is a tab called Min Max and I'm pulling the information to an entirely separate tab.

Hope this helps...