# Excel Formula Help

#### Kody8914

##### New Member
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
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
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
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
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!

Replies
0
Views
260
Replies
6
Views
341
Replies
6
Views
677
Replies
0
Views
422
Replies
3
Views
181

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?

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