Help with costing formulas

NoelD

New Member
Joined
Apr 16, 2015
Messages
45
Office Version
  1. 2019
Platform
  1. Windows
I am building a car RENTAL schedule based on three possible terms, each with two possible advance payments. The BUYBACK is calculated based on the same premise as the Rental.

I have been unable to create a working formula for the scenario. I have a spreadsheet image attached with my formula [ that does not work ]

Any help would be fantastic

Noel
 

Attachments

  • Mr E Question.jpg
    Mr E Question.jpg
    134 KB · Views: 7
If I'm following your requirement correctly then VLOOKUP is not suitable for what you are trying to do.

Try this one on O19 (note that P19 must be empty, or you will still get a #SPILL! error.

=N19*INDEX($I$11:$N$12,MATCH(J19,$H$11:$H$12),MATCH(K19,$I$9:$N$9)+{0,1})/1000
 
Upvote 0
I have some questions about how the Term/Advance values match up with the table. Your term (the J19 value) - since it's a 2, do you mean the second term listed, i.e. K11:L12? Or does the 2 match with the I9 value? For the Advance (K19), does it match with the H11:H12 values? If so, shouldn't you be looking at row 11 for a result? If not, how you you choose which row to use?

Here's an example using my current assumptions:

Book1
GHIJKLMNOPQ
9TermTerm2Years3Years4Years
10FactorsInvoiceBuybackInvoiceBuybackInvoiceBuyback
11Advance144.3138.7130.4824.9623.5918.1
12Advance343.8638.3230.1724.723.3417.91
13
14
15
16
17TERMADVANCESTEP 1STEP 2STEP 3STEP 4
18PriceVRTBuyback amtTerm >YearsRentals in AdvanceVRT FactorIFCVFFCInvoice FactorBuyback Factor
1940000550016000210.186991265412651257.757399.36858.3972
Sheet5
Cell Formulas
RangeFormula
O19O19=N19*INDEX($I$11:$N$12,MATCH($K$19,$H$11:$H$12,0),$J$19*2-1)/1000
P19P19=I19*INDEX($I$11:$N$12,MATCH($K$19,$H$11:$H$12,0),$J$19*2)/1000
Q19Q19=O19-P19
 
Upvote 0
I have some questions about how the Term/Advance values match up with the table. Your term (the J19 value) - since it's a 2, do you mean the second term listed, i.e. K11:L12? Or does the 2 match with the I9 value? For the Advance (K19), does it match with the H11:H12 values? If so, shouldn't you be looking at row 11 for a result? If not, how you you choose which row to use?

Here's an example using my current assumptions:
 
Upvote 0
Hi Eric

Firstly, thank you for your assistance.

To Clarify J19 = 2, which refers to Column I9 and the Advance number in K19, refers to I11. It was that part of the formula that I failed with as the Terms and Advance numbers changed.

In relation to the Buyback formula, for this case , would use J11. Can you have the formula work when changing the term number and advance in Cells J19 & k19 will read the appropriate rates from teh table.

If you could rework your magic, that would be great.

Noel
 
Upvote 0
Hi Eric

Firstly, thank you for your assistance.

To Clarify J19 = 2, which refers to Column I9 and the Advance number in K19, refers to I11. It was that part of the formula that I failed with as the Terms and Advance numbers changed.

In relation to the Buyback formula, for this case , would use J11. Can you have the formula work when changing the term number and advance in Cells J19 & k19 will read the appropriate rates from teh table.

If you could rework your magic, that would be great.

Noel
this image has the answer that should be achieved....if you can work your magic formulas
 

Attachments

  • q2 IMAGE.jpg
    q2 IMAGE.jpg
    98.9 KB · Views: 1
Upvote 0
Try:

Book1
GHIJKLMNOPQ
9TermTerm2Years3Years4Years
10FactorsInvoiceBuybackInvoiceBuybackInvoiceBuyback
11Advance144.3138.7130.4824.9623.5918.1
12Advance343.8638.3230.1724.723.3417.91
13
14
15
16
17TERMADVANCESTEP 1STEP 2STEP 3STEP 4
18PriceVRTBuyback amtTerm >YearsRentals in AdvanceVRT FactorIFCVFFCInvoice FactorBuyback Factor
1940000550016000330.186991265412651244.965395.2849.7651
201244.965395.2
Sheet5
Cell Formulas
RangeFormula
P19P19=I19*INDEX($I$11:$N$12,MATCH($K$19,$H$11:$H$12,0),MATCH($J$19,$I$9:$N$9,0)+1)/1000
Q19Q19=O19-P19
O19O19=N19*INDEX($I$11:$N$12,MATCH($K$19,$H$11:$H$12,0),MATCH($J$19,$I$9:$N$9,0))/1000
O20:P20O20=INDEX(I19:N19,{6,1})*INDEX($I$11:$N$12,MATCH($K$19,$H$11:$H$12,0),MATCH($J$19,$I$9:$N$9,0)+{0,1})/1000
Dynamic array formulas.


The O19, P19 formulas are single cell formulas, while the O20 is a SPILL formula if you prefer, although I think the 2 formulas are clearer in this case.
 
Upvote 0

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
Back
Top