Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
SOLVED

Iron Contributor

# Query related to excel

Hello Everyone,

How can I achieve the following calculation in a formula?

If the house value is 750,000 or less the maximum mortgage amount allowed is 80% of that value. If the house value is more than 750,000 then the maximum mortgage allowed is 80% of the first 750,000 and 50% of the remainder. Example:

House value is 1,000,000 then maximum mortgage allowed is (750,000*80%)+(250,000*50%)= 725,000

7 Replies
best response confirmed by Excel (Iron Contributor)
Solution

# Re: Query related to excel

@Excel Let's say the value is in A1, try this:

``=MIN(750000,A1)*80%+MAX(0,A1-750000)*50%``

# Re: Query related to excel

Alternatively:

=80%*A1-30%*MAX(A1-750000,0)

# Re: Query related to excel

Thank you so much sir.  Sir, one more question,

I want to apply VLOOKUP FUNCTION formula in filtered cells only with the help of VBA code. So, what should i write VBA code to give VLOOKUP FUNCTION and it should be dynamic ??

Here is a attached file..

# Re: Query related to excel

You keep on asking the same question:

# Re: Query related to excel

Sorry sir,
Actually i solved in with Excel functions, but i do not know how to do in VBA code ?

# Re: Query related to excel

Have you tried recording a macro? That might provide a start - you'll have to edit the recorded macro to make it more generic.

# Re: Query related to excel

Thank you so much sir.
When i do macro, it give me the VBA code.
Thank you so much sir.
1 best response

Accepted Solutions
best response confirmed by Excel (Iron Contributor)
Solution

# Re: Query related to excel

@Excel Let's say the value is in A1, try this:

``=MIN(750000,A1)*80%+MAX(0,A1-750000)*50%``