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

 

[BlondieC]

I'll be back in the office Friday and will give this a try - if I can get this last piece of my workbook there will be some very happy people. I wasn't aware of using the F2 key for this type of array - what does it do or activate?

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...

 

[jeffreybrown]

F2 is used to activate the cell.

If you place your cursor on any cell and select F2 you simply activate that cell. Your cursor will not be within the cell.

I wasn't aware of using the F2 key for this type of array - what does it do or activate?

F2 can be used with any cell and any type of formula.

 

[BlondieC]

Jeffrey, I actually had a blonde moment re theF2 key. It dawned on me after how often I have used it to edit thecurrent cell contents.

I tookthe line of code you provided and adapted it for my workbook and was able tosee it work. However, in my excitement of coming across this thread Ileft out one of the other variables I should have asked about. I'll givesome background...
-Workbookwith 6 standard worksheets and additional multiple worksheets that will varyworkbook to workbook.
-Theworkbook functions as follows:
-WorkingData worksheet containsaddress information, a combo of VBA and macros are available and one of theresults produces a concatenated list that contains the street name and streettype ie Albany St.
-StreetRangeData worksheet hasa combo of VBA and macros available - macro1 (for simplicity) copies/pastes theconcatenated street name list from WorkingData worksheet into StreetRangeData worksheet A2:A. Macro 2 reads through the new list in A2:A and produces a new worksheetfor each street name in the list and gives the new worksheet that street name. For each street name in the list I now have a worksheet by that name thatis based on a template worksheet so the contents and functionality are all thesame and the names in the list are now hyperlinked to the matching worksheet.
-Albany St worksheet andall the other street named worksheets have a command button that grabs all datafrom specific columns on the WorkingData worksheet andpastes it in the specified location of the street named worksheet. SoAlbany St worksheet will only have info for Albany St and Andrew St worksheet will only have info for Andrew St etc.

Thisbrings me to where the code you provided may come in.
What Iwould like to do in from each individual street named worksheet, look at thecivic address number data filed and return the Max and Min for the even rangeand the odd range - 4 number values total, to the StreetRangeData worksheet andpaste those values in columns B, C, D, E which are all to the right of column Awhich is holding the street name.

The part I left out in my excitement was the even and odd range. Your code findsthe Min and Max but can it be split further to find Even Min Max and Odd MinMax?

Thank you

F2 is used to activate the cell.

If you place your cursor on any cell and select F2 you simply activate that cell. Your cursor will not be within the cell.



F2 can be used with any cell and any type of formula.