Please Help: Max Function with Multiple Criteria

[dsv2kx]

Years
Mortgage 15 20 25 30 35
$250,000 $1,830.48 $1,495.26 $1,298.97 $1,172.02 $1,084.56
$260,000 $1,903.70 $1,555.07 $1,350.93 $1,218.90 $1,127.94
$270,000 $1,976.92 $1,614.89 $1,402.89 $1,265.78 $1,171.32
$280,000 $2,050.14 $1,674.70 $1,454.85 $1,312.66 $1,214.70
$290,000 $2,123.36 $1,734.51 $1,506.81 $1,359.54 $1,258.09
$300,000 $2,196.58 $1,794.32 $1,558.77 $1,406.42 $1,301.47
$310,000 $2,269.80 $1,854.13 $1,610.73 $1,453.31 $1,344.85
$320,000 $2,343.02 $1,913.94 $1,662.69 $1,500.19 $1,388.23
$330,000 $2,416.24 $1,973.75 $1,714.65 $1,547.07 $1,431.61
$340,000 $2,489.46 $2,033.56 $1,766.61 $1,593.95 $1,475.00
$350,000 $2,562.68 $2,093.37 $1,818.56 $1,640.83 $1,518.38

Maximum Affordable Monthly Payment $1,500

# years Max. Mortgage
15
20 $250,000.00
25 $280,000.00
30 $310,000.00
35 $340,000.00


Using the values in the grid above, I am trying to determine the largest possible mortgage the client can afford and fill in the maximum mortgage amounts in the table, but I'm having trouble find the right multi-conditional IF formula. For example, at a 30 year mortgage, the maximum the client can afford under $1500 a month is a $310,000 mortgage. Please help me come up with the right IF formula to make Excel determine the largest possible mortgage.

[:) Sixthsense :)]

Hi dsv2kx,

Welcome to the forum

Please attach a sample workbook with expected result to get for better understanding of your data structure

Please refer my below signature area to know how to attach a file

[samba_ravi]

see the attachment, it may help for you

[:) Sixthsense :)]

For example, at a 30 year mortgage, the maximum the client can afford under $1500 a month is a $310,000 mortgage

1500 per month
30 Years

30 years x 12 months = 360 Months

360 Months x 1500 = 5,40,000

Can you please explain?

[kvsrinivasamurthy]

Pl see attached file with formula.

[:) Sixthsense :)]

In Addtion of kvsrinivasamurthy's E18 cell formula

Please Login or Register  to view this content.