Looking for dynamic formula solution [date & amount range] [Excel 2013]

kachaloo

New Member
Joined
Jan 18, 2011
Messages
22
Office Version
  1. 2016
Platform
  1. Windows
Hello I have spreadsheet with two table where I record the Cars bought via auction site
Table 1 called "Stock" with car reg numbers, model and purchase price and then pull BuyerPremium cost from BuyerPremum table.

Table 2 is "BuyerPremium" with all the amount range like 0-49, 50-99, 100- 499, 500-999, 1000-4999 and son on till 100,00. and Buyer premium FEE on cars bought in range.

I already have this array formula and working on.
Excel Formula:
{=IF(ROUND(W1035,0)<=0,0,IF([@opDate]>=BuyerPremium!$G$2,INDEX(Jan20Fee,MATCH(1,IF(FromPremium<=ROUND(W1035,0),IF(ToPremium>=ROUND(W1035,0),1)),0)),INDEX(Aug19Fee,MATCH(1,IF(FromPremium<=ROUND(W1035,0),IF(ToPremium>=ROUND(W1035,0),1)),0))))}

opDate = original purchase date
W1035 = HammerPrice/purchase
FromPremium = namerange b2:b46
ToPremium = name range c2:c46
Aug19Fee = name range e2:e46
Jan20Fee = name range g2:g46

Not part of the formula above yet . but looking better option yet.
Sep20Fee = name range i2:i46
Jan21Fee = name range k2:k46

The problem is that the Auctioneer changes the premium twice a year.

How can I use/design this table so when new fee are added from future dates and the index/match formula just pull the right values based on Date and From-To auction prices.

Thank in advance.

Using Excel Professional Plus 2013
 

Attachments

  • premium.JPG
    premium.JPG
    135.1 KB · Views: 12

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
You can do that without an array formula like
+Fluff New.xlsm
BCDEFGHIJKL
101/01/202001/08/202001/01/202101/09/2021
204912617014311615010/11/2020173
35099123177130185
4100199166173150191
5200299173174109181
6300499159198116150
7500999103195181169
8
9
Data
Cell Formulas
RangeFormula
L2L2=INDEX($D$2:$I$9,MATCH(J2,$B$2:$B$9,1),MATCH(K2,$D$1:$I$1,1))
 
Upvote 0
Wow. Thank you for really simple formula.
Next questing related to this if you can help again please.
How to show the values as Minus when there is credit note. i.e
Purchase price is -150 in J2? so it should show L2 as -173.

Thanks again.
 
Upvote 0
How about
Excel Formula:
=INDEX($D$2:$I$9,MATCH(ABS(J2),$B$2:$B$9,1),MATCH(K2,$D$1:$I$1,1))*SIGN(J2)
 
Upvote 0
Perfect. Thank you.
Now I will need to learn how ABS works.
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,315
Members
448,564
Latest member
ED38

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