Excel Formula Help

Kody8914

New Member
Joined
May 29, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
I am a realtor and I am trying to create an excel sheet for comparing multiple offer scenarios.

Here are the offers

Offer 1
$200,000
Maximum offer of $225,000
Escalation intervals of $500

Offer 2
$205,000
Maximum offer of $210,000
Escalation intervals of $100
buyer asking for $4500.00 in closing cost
Basically the offer of this is now $200,500

Offer 3
$200,000
Maximum offer of $211,000
Escalation intervals of $1,000

Basically I am looking for a formula that can show escalated offers in a cell for easy comparison as to which one is the winner. So if we did it manually we would know that offer 1 would win because they can go up to $225,000 but the final price would only be $211,500 because of the $500 escalation interval of offer 1 and only needing to exceed offer 3's maximum offer by $500.

Hopefully this is possible with a formula as it would really take all the work out of manually piecing it together. I have attached a screen shot of my spreadsheet for reference. The green line is where I want the formula to automatically show the offer with escalations in play. The offers do not need to exceed the maximum offer in all cases.
 

Attachments

  • Multiple Offer Comparison Sheet.PNG
    Multiple Offer Comparison Sheet.PNG
    103.9 KB · Views: 7

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
934
Office Version
  1. 365
Platform
  1. Windows
Basically I am looking for a formula that can show escalated offers in a cell for easy comparison as to which one is the winner. So if we did it manually we would know that offer 1 would win because they can go up to $225,000 but the final price would only be $211,500 because of the $500 escalation interval of offer 1 and only needing to exceed offer 3's maximum offer by $500.
Could you please explain me this with an example

also consider posting the data using xl2bb addin available in right hand side of reply button
 

petertenthije

Active Member
Joined
Sep 25, 2012
Messages
266
Basically what I did is:
1) I made a ranking of the bids from high to low. That is the bid minus closing costs. (column A)
2) The best bid is obviously the winner. (column D)
3) The runner up bid is the base price.
4) I added the interval of the winner, to the best bid of the runner up. (cell G2)


Book1
ABCDEFG
1RankMAX OFFERCLOSING COSTMAX OFFER minus CLOSING COSTINTERVALFINAL BID
21$ 225.000,00$ 0,00$ 225.000,00$ 500,00$ 211.500,00
33$ 210.000,00$ 4.500,00$ 205.500,00$ 100,00
42$ 211.000,00$ 0,00$ 211.000,00$ 1.000,00
Sheet1
Cell Formulas
RangeFormula
G2G2=VLOOKUP(2,$A:$E,4,FALSE)+VLOOKUP(1,$A:$E,5,FALSE)
A2:A4A2=RANK($D2,$D$2:$D4,0)
D2:D4D2=B2-C2
 

Kody8914

New Member
Joined
May 29, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Basically what I did is:
1) I made a ranking of the bids from high to low. That is the bid minus closing costs. (column A)
2) The best bid is obviously the winner. (column D)
3) The runner up bid is the base price.
4) I added the interval of the winner, to the best bid of the runner up. (cell G2)


Book1
ABCDEFG
1RankMAX OFFERCLOSING COSTMAX OFFER minus CLOSING COSTINTERVALFINAL BID
21$ 225.000,00$ 0,00$ 225.000,00$ 500,00$ 211.500,00
33$ 210.000,00$ 4.500,00$ 205.500,00$ 100,00
42$ 211.000,00$ 0,00$ 211.000,00$ 1.000,00
Sheet1
Cell Formulas
RangeFormula
G2G2=VLOOKUP(2,$A:$E,4,FALSE)+VLOOKUP(1,$A:$E,5,FALSE)
A2:A4A2=RANK($D2,$D$2:$D4,0)
D2:D4D2=B2-C2
This works perfectly, thank you very much!
 

Forum statistics

Threads
1,137,124
Messages
5,679,746
Members
419,855
Latest member
Eddier32

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
Top