Photo by Christopher Burns on Unsplash | How to skip over blanks in Coda?

How to skip over blanks in Coda?

Pro stuff: getting the next value in line

I noticed in my VAT database some countries with only two or three VAT variations like Danmark: 0% and 25%

The VAT is a sales tax that applies to the purchase of most goods and services, and must be collected and submitted by the merchant to the Denmark governmental revenue department.

Denmark’s VAT rate of 25% ranks as one of the ten highest VAT rates in the world. Countries with similar VAT rates include Iceland with a VAT of 26%.

On the other hand countries like Belgium have values for all variations, while the Netherlands has the reduced VAT (was 6% a few years ago) and skips the Reduced variation. When a VAT type has no value, it is blank and in this scenario we need the next in line, not the previous one. Governments need their tax! For the Netherlands this implies that a reduced VAT rate from Belgium is translated into a standard VAT in the Netherlands.

The problem we solve in this blog is how you find the next value in line?

To make the issue understandable and applicable outside the VAT context for which I need it, we focus on a simplified version of the matter. In case we find nothing — IsBlank() — we skip and take the next value in line and if there is no next value, we take the last one.

How to skip and get the next value in line

To make things easier — and possible — we add an extra column with a row index and we use this index to see which of the rows contain a value and which don’t. This permits us to aks for the letter at position 4 or 5 for example.

Step one: create a row index

The formula is a filter like below:

finding the letter related to the position

Our next step is to use a text box to enter numbers. We reference it in the filter for oversight and flexibility:

we use a text box and we add the IfBlank()

The Ifblank() permits us to show a value once the outcome is blank. We took number 5, the answer should be ‘D’ but for now we have ‘create something’. It is this ‘something’ we consider as our little puzzle to solve.

The value for a blank is based on an existing value, thus on IsNotBlank(). This leads us to the formula below. We need a letter related to one of these positions.

We take the outcome of the filter and compare it to the value in the text box using a FormulaMap():

´getting the positions

We notice 2 numbers greater than, because we see true, true. How can we get the first ‘true’ out of this range? It took me a a bit of time to see it and the fun is that once you see it, you wonder why you did not see before. Here we go:

using Find() to get the position of the first true

The Find() function gives us the position of the first true, it is position 4 of the list that outputs the values that are not blank.

Once we have this position, it is time to apply it in a filter that takes into account this position in the list. Positions are referenced via Nth() and this is what you see below. We create a list of non blanks and we ask for the letter on the position we just found

This is quite a formule blending various functions to get something simple as the next value in line.

This brings us back to my initial request on finding the next in line VAT value for which i needed this formula as part of a a larger code snippet. The row index in the above part is replaced by a group index and we have some additional criteria, but it all boils down to a combination of Filters, Formulamap and Find (FFF).

applied in the context of VAT logic

The interesting thing in Coda is that you keep on learning and you even notice your own acceleration in solving puzzles of all sorts.

I hope you enjoyed this article. If you have questions feel free to reach out. Though this article is for free, my work (including advice) won’t be, but there is always room for a chat to see what can be done. Besides you find my (for free) contributions to the Coda Community and on Twitter

My name is Christiaan Huizer and I am the owner of Huizer Automation. A company specialized in serving SME in harvesting data and keeping it aligned. I am a Coda Consultant & Expert and rely mainly on Coda, Mailjet, Zapier & Paperform to get the job done.

Huizer Automation — Coda Expert and Consultant on “How to skip over blanks in Coda?”

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store