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

kachaloo

New Member
Joined
Jan 18, 2011
Messages
21
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: 2

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,139
Office Version
  1. 365
Platform
  1. Windows
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))
 

kachaloo

New Member
Joined
Jan 18, 2011
Messages
21
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,139
Office Version
  1. 365
Platform
  1. Windows
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)
 

kachaloo

New Member
Joined
Jan 18, 2011
Messages
21
Office Version
  1. 2016
Platform
  1. Windows
Perfect. Thank you.
Now I will need to learn how ABS works.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,139
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,995
Messages
5,599,256
Members
414,299
Latest member
thenewworld

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