MIN & MAX Formula with Multiple Variables

marko0223

New Member
Joined
Aug 6, 2019
Messages
7
I am trying to get the MIN loan amount and the MAX loan amount in the data I have. I need to find them within a date range and I need the Freddie and Fannie loans as one group and FHA loans in their own group within the same date range. Some of the FHA loans are on 2 different sheets. Is there a way to do this? If this doesn't make sense I can try and clarify. Here is an example of the data:


Sheet 1

1/5/19 Fannie $1,000,000.00
1/6/19 Freddie $8,000,000.00
1/7/19 Fannie $4,000,000.00
1/7/19 FHA $10,000,000.00

Sheet 2

1/9/19 FHA $11,000,000.00
1/11/19 FHA $3,000,000.00


Sheet 3

This is where my formula is going.
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Can anyone help me with this?





I am trying to get the MIN loan amount and the MAX loan amount in the data I have. I need to find them within a date range and I need the Freddie and Fannie loans as one group and FHA loans in their own group within the same date range. Some of the FHA loans are on 2 different sheets. Is there a way to do this? If this doesn't make sense I can try and clarify. Here is an example of the data:


Sheet 1

1/5/19 Fannie $1,000,000.00
1/6/19 Freddie $8,000,000.00
1/7/19 Fannie $4,000,000.00
1/7/19 FHA $10,000,000.00

Sheet 2

1/9/19 FHA $11,000,000.00
1/11/19 FHA $3,000,000.00


Sheet 3

This is where my formula is going.
 
Upvote 0
Do you have maxifs available in your excel version? What are the date ranges? Are they typed in cells or being hardcoded?
 
Upvote 0
I do not have maxifs unfortunately. The date range is from 1/1/17 to current day. I have the beginning and end date in their own cells that I can change depending on what months I need the information from.







Do you have maxifs available in your excel version? What are the date ranges? Are they typed in cells or being hardcoded?
 
Upvote 0
Could you provide the expected results and how do you want them displayed in Sheet3?

M.
 
Upvote 0
On Sheet 3 I just want the highest loan amount and the lowest loan amount displayed. So Sheet 3 would look like:

Highest Loan Amount(Fannie & Freddie) $8,000,000.00 Lowest Loan Amount(Fannie and Freddie) $1,000,000.00

Highest Loan Amount (FHA) $11,000,000.00 Lowest Loan Amount (FHA) $3,000,000.00
 
Upvote 0
Maybe something like this

Sheet1

A
B
C
1
Date​
Name​
Value​
2
1/5/19​
Fannie​
1000000,00​
3
1/6/19​
Freddie​
8000000,00​
4
1/7/19​
Fannie​
4000000,00​
5
1/7/19​
FHA​
1000000,00​
6

<tbody>
</tbody>


Sheet2

A
B
C
1
Date​
Name​
Value​
2
1/9/19​
FHA​
11000000,00​
3
1/11/19​
FHA​
3000000,00​
4

<tbody>
</tbody>


Sheet3

A
B
C
D
E
F
1
Name​
Min​
Max​
StartDate​
EndDate​
2
Fannie & Freddie​
1000000,00​
8000000,00​
1/1/17​
8/8/19​
3
FHA​
1000000,00​
11000000,00​

<tbody>
</tbody>


F2
=TODAY()

Array formula in B2
=MIN(IF(Sheet1!A2:A100>=E2,IF(Sheet1!A2:A100<=F2,IF(ISNUMBER(SEARCH(Sheet1!B2:B100,A2)),Sheet1!C2:C100))))
confirmed with Ctrl+Shift+Enter, not just Enter

Array formula in C2
=MAX(IF(Sheet1!A2:A100>=E2,IF(Sheet1!A2:A100<=F2,IF(ISNUMBER(SEARCH(Sheet1!B2:B100,A2)),Sheet1!C2:C100))))
confirmed with Ctrl+Shift+Enter, not just Enter

Array formula in B3
=MIN(MIN(IF(Sheet1!A2:A100>=E2,IF(Sheet1!A2:A100<=F2,IF(Sheet1!B2:B100=A3,Sheet1!C2:C100)))),MIN(IF(Sheet2!A2:A100>=E2,IF(Sheet2!A2:A100<=F2,IF(Sheet2!B2:B100=A3,Sheet2!C2:C100)))))
confirmed with Ctrl+Shift+Enter, not just Enter

Array formula in C3
=MAX(MAX(IF(Sheet1!A2:A100>=E2,IF(Sheet1!A2:A100<=F2,IF(Sheet1!B2:B100=A3,Sheet1!C2:C100)))),MAX(IF(Sheet2!A2:A100>=E2,IF(Sheet2!A2:A100<=F2,IF(Sheet2!B2:B100=A3,Sheet2!C2:C100)))))
confirmed with Ctrl+Shift+Enter, not just Enter

Hope this helps

M.
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,176
Members
448,554
Latest member
Gleisner2

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
Back
Top