Multiple if for getting a result - help!

[smanno129]

I have no idea where to begin or if it is even possible in excel to complete. I work for a mortgage company and we are looking to create a form (chart or even just field of cells) that we can use to determine max loan amount based on total acquisition cost. Here is a breif explanation of what I need..


Sales price/Lot cost
+ hard costs/cost to construct
+ soft costs
_____
= Total acquisition cost.


Based on Acquisition cost:
If acquisition cost is <= 1250000, multiply acquisition cost by 80%

If acquisition cost is > 1250000, and <= 2000000, multiply acquisition cost by 75%; If the answer is <1000000, round number up to 1000000; ONLY WHEN MULTIPLIED BY 75%

If acquisition cost is > 2000000, and <= 2857100, multiply acquisition cost by 70%; If the answer is <1500000, round number up to 1500000; ONLY WHEN MULTIPLIED BY 70%

If acquisition cost is > 2857100, and <= 4615300, multiply acquisition cost by 65%; If the answer is < 2000000, round number up to 2000000; ONLY WHEN MULTIPLIED BY 65%


So, if the AC is multiplied by 75% and comes out to be less than 1500000, i do not want it to be rounded up to 1500000 because this rounding up only applies to when multiplied by 70%. Example: AC is $1,672,000. Excel does multiply by 75% like it is supposed to, but because the result ends up $1,254,000, excel is pulling the rule applied specifically for 70%, and rounding it up....

I'm so confused! Help please!

[ConneXionLost]

Untested, but try:

Please Login or Register  to view this content.

Cheers,

[smanno129]

I tested it, and it works for the muliplying by percentage part, but does not round like I need it to.

[Sam Capricci]

I would do it in two steps (not as good as ConneXionLost )
Step 1, use this in cell B2=IF(A2<1250001,A2*0.8,IF(A2<2000001,A2*0.75,IF(A2<2857101,A287*0.7,A2*0.65)))
Step 2, use this in cell C2=IF(B2<1250001,B2,IF(B2<1000000,1000000,IF(B2<1500001,1500000,2000000)))
Hope I got your numbers correct.

EDIT: BTW, tested and appears to work.

[smanno129]

I tested it, and the information in cell c2 does not round, it stays the same as b2 .....

[oeldere]

A B C

0 80%
1250000 0% 1000000
1333333 75%
2000000 0% 1500000
2142857 70%
2857100 0% 2000000
3076923 65%
4615300

after that you can use a vlookup function.

[Sam Capricci]

I can only guess at what you have in C2 but based on what you wrote my version appears to work. Again, i'm working with my "made up" data and my interpretation of what you wrote. If oledere didn't hit it and i didn't and ConneXionLost didn't, so we don't continue to struggle, maybe a sample spreadsheet would help.

[smanno129]

A B C

0 80%
1250000 0% 1000000
1333333 75%
2000000 0% 1500000
2142857 70%
2857100 0% 2000000
3076923 65%
4615300

after that you can use a vlookup function.

I'm not familiar with Vlookup, also had to change the wording of the question to be more specific as to which rounding rule applies to which multiplying percentage

[smanno129]

I got the second formula to work, but am now running into the problem of which rounding rule applies to which multiplying percentage. I changed the wording in my post to reflect this.

I could do a sample spreadsheet, but not sure how to?

[ConneXionLost]

After reading your original OP and testing my formula, I still seem to be getting the results you asked for. $1,672,000 does result in $1,254,000 (not $1,500,000). Is it possible you copied/edited my formula incorrectly?

[Sam Capricci]

to post a sample sheet, use the go advanced button below, then mid way down the sheet is a "manage attachments" button. Hint, easiest to save a sample to your desktop to find it quickly when browsing.

[smanno129]

to post a sample sheet, use the go advanced button below, then mid way down the sheet is a "manage attachments" button. Hint, easiest to save a sample to your desktop to find it quickly when browsing.

222.xlsx

I am using the same formula that you gave me, only changing "c2" to "b6"

[Sam Capricci]

Ok, so what is the answer supposed to be so I can see where it is off?

EDIT: is one calculation correct and the other wrong? Or both? if one, which and what should it be?

[smanno129]

"max loan" should be 1254000, because it was multiplied by 75%. The "max loan" cell still rounded up to 1.5 MM, which this rule only applies when "AC xLTV" is multiplied by 70%

[smanno129]

AC x LTV is calculating correctly,

Max Loan is applying the rounding rules, no matter which amount "acxltv" is multiplying by, which is wrong.

[oeldere]

Please Login or Register  to view this content.

See the attached file.

[Sam Capricci]

so, is this what you're looking for?