In Calc, lookup values in date ranges by start date

[skierpage]

Hi, “The Authorities” tell me the sales tax rate starting on 2016-07-01 is 8.25% , starting on 2018-07-01 it is 8.5%, and starting on 2020-07-01 it is 8.75%. I want to use the sales tax that applied on a date, e.g. 2018-07-01, in a formula.

So this is “lookup closest value less than or equal to date (2018-07-01) then return the value in the column next to it (8.5%).” I just read about VLOOKUP and it’s really close, hooray! :

if the exact SearchCriterion is not found, the last value that is smaller than the criterion will be returned.

That means that if the sale date is the start date of a new tax rate, VLOOKUP will return the sales tax for the previous period. So I think all I have to do is have a column “After this day the sales tax changed” containing 2016-06-30, 2018-06-30, etc. and VLOOKUP() on that. Or is there a cleaner way?

It seems another way to do this would be to create a user-defined function SalesTaxOnDate(date) with a series of IF statements in it.

[EarnestAl]

Vlookup has an option, SortedRangeLookup. Enter FALSE or 0 as the last parameter and it will search for only an exact match, returning an error if not found.

[newbie-02]

hello skierpage,

It seems another way to do this would be to create a user-defined function SalesTaxOnDate(date) with a series of IF statements in it.

that is … less advisable? … macro-functions sometimes have problems with the refresh on changes, and ‘nested-ifs’ are … uh … not very clear …

where, you have only one case and don’t have to dynamically adapt it for other countries, dates or cases? put it in one drop-down formula, that’s quick and dirty but works, see sheet1 in the example,

256626_tax_calculator_test.ods

with vlookup (hlookup works the same way) it is only a question of the correct placement of the data, see sheet2 in the example,

That means that if the sale date is the start date of a new tax rate, VLOOKUP will return the sales tax for the previous period.

nope, in that case the exact search criterion will be found,

just for fun and for like of @Mike2 and to learn something for myself i’d try a solution with ‘sumproduct’ in sheet3, be careful, it looks like working but is only ‘trial-and-error-crafted’ …

(i’d not combine formulas to find percentage and calculate amount to keep the steps clearly visible, of course you may change that for your case),

(if this answers your question, please mark the ‘solved’ check mark left to the answer, if you like the answer give it a ‘^’ directly above it)

reg.

b.

[mikekaganski]

Oh my. Why you insist on this stubborn and cruel pinging of an innocent person instead of using correct addressee! Yes I assume you meant me when wrote that @Mike2 (if not, I apologize, but you keep pinging that Mike again and again even when discussing something in our conversations, and now I see you even do that in random places! Why not learn at last to write @mikekaganski, and not nag unknown people who don’t deserve such treatment from you?)

[newbie-02]

hello @mikekaganski,

sorry, me bad, see explanation and apologies there: explanation and apologies

i’d - intentionally!! - chose my nickname to point out that:

• i’m not! perfect,
• i’m aware of that,
• think i’ll never be, and
• warn others about that shortcoming

[JohnSUN]

You can try

=LOOKUP(TEXT(<your date>;"YYYY-MM-DD"); {"2016-07-01","2016-07-01","2016-07-01"}; {0.0825,0.085,0.0875})

or

=LOOKUP(<your date>; {42552:43282:44013}; {0,0825:0,085:0,0875})

Use the separators that are specified in your settings - they can be different

[mikekaganski]

You are given good answers already. So this is not another answer, as it kind of repeats what is already told, and is just intended to clarify one point.

So this is “lookup closest value less than or equal to date (2018-07-01) then return the value in the column next to it (8.5%).” I just read about VLOOKUP and it’s really close, hooray! :

if the exact SearchCriterion is not found, the last value that is smaller than the criterion will be returned.

That means that if the sale date is the start date of a new tax rate, VLOOKUP will return the sales tax for the previous period. So I think all I have to do is have a column “After this day the sales tax changed” containing 2016-06-30, 2018-06-30, etc. and VLOOKUP() on that. Or is there a cleaner way?

VLOOKUP in its SortedRangeLookup mode is designed exactly for the task of choosing correct range from a sorted list or ranges, which consists of start points for each range. In your case, the list should be “dates when a given tax became effective”, sorted ascending. Then for any date, it will find the minimal element in the list which is no less than that date. So for dates equal to, or greater than a given date, you will find the proper entry in your list, and so get the proper tax effective for that moment.

The usage was clarified in the VLOOKUP function help in v.6.4 compared to v.6.3, to make this specific use pattern clear.

[mikekaganski]

EffectiveTax.ods