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.
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
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