IF and AND formula

#1

Hi everyone

I am trying to create an if statement in my formula so if ‘Something A’ is true and ‘Something B’ is true then display ‘THIS’

Now my if statement works i.e.

IF( 
	[VAT Percentage].IsBlank(), 
		'Yeah',
		'oh'
)

but my IF /AND doesn’t, what am I doing wrong?

IF(
	[Is VAT Included in the Cost?]=true,
AND(
	[VAT Percentage].IsBlank()
), 
	'Yeah',
IF(
	[Is VAT Included in the Cost?]=false,
AND(
	[VAT Percentage].IsBlank()
), 
	'No'
))
#2

Never mind.

The following appears to work if I drop the ‘IF’

And(
	[Is VAT Included in the Cost?]=true,
	[VAT Percentage].IsBlank()
)
#3

Although this works, it doesn’t allow me to use a value i.e.

If [Is VAT Included in the Cost?]=true, AND [VAT Percentage].IsBlank() THEN OUTPUT ‘Yeah’ otherwise ‘Oh’

I only get true or false.

#4

@Juanmata

if(and([column1]=true, [column2].IsBlank()),"Yeah","oh")

#5

@Ander

Thank you for that.

I just worked it out just before you posted but thank you as it confirms I was on the right path.

1 Like
#6

I’m still actually stuck on this.

Basically, I am trying to add a receipts table, sometimes the receipt has vat included, sometimes not. I’m also trying to think ahead so that if the government changes the vat rate I can manually change it.

On this bases, I have the following columns.

• Is Vat Included (Checkbox)
• Cost (Manually add the amount with or without vat)
• VAT (This will calculate the vat amount, it may need to add it, it may need to take it away)
• VAT Override (The VAT formula will be based at 20%, if i add another figure here, it will override the 20%)
• Total (Cost + VAT = TOTAL)

I need my formula look to see if the VAT is included, if true then add the vat, if false then minus the vat. Now if the VAT override has a figure, I need it to override the vat rate in the formula with the one in the cell.

So far I have the following:

IF(
	[Is VAT Included in the Cost?]=true,
		'COST+20%',
		'COST-20%',
	IF(
		[Is VAT Included in the Cost?]=true,
		[VAT Percentage].IsNotBlank(),
			'VA+20%',
	),
	IF(
		[Is VAT Included in the Cost?]=false,
		[VAT Percentage].IsNotBlank(),
			'VA-20%',
	)
)

(Ignore the actual calculations, I was just trying to make it easier to read)

The third if statement I believe is needed because, if [Is VAT included] = true and [VAT Percentage] = 40% the second IF statement will work, but if [Is VAT included] = false, then it would break.

I’ve tried all lnight trying to get this to work and I keep getting errors. I had this nailed in Airtable but I can’t quite figure out Coda yet. It would help if they had actual examples in the formula docs.

#7

@Juanmata

I didn’t completely understand your post, probably because I don’t deal with VAT. But I tried to infer what you were after. Is it something like this?

1 Like
#8

@Ander

Thank you, that worked however you have given me an idea with your layout that wasn’t possible with Airtable but now looks possible with Coda.

You’ve added another table for the VAT rate, rather than have just one line with VAT @ 20%, is it possible to have multiple lines as the government has changed it multiple times since I have been in business.

i.e.
Between 01/01/2017 - 31/12/2017 - Vat Rate 15%
Between 01/01/2018 - 31/12/2018 - Vat Rate 17.5%
Between 01/01/2019 - Vat Rate 20%

This way, I do not have to manually override anything and all expenses dated in a period will automatically be assigned that VAT rate, Is this possible?

Also, rather than have a checkbox to ask if VAT is included, can you have a two way formula? What I mean is

My table columns: COST | VAT | TOTAL
If I add a COST price, it will add the vat (in the VAT box) and show the total (in the TOTAL box), If I add the TOTAL price, it will takeaway the vat (in the VAT box) and show the cos t(in the COST box)? This way would work the best rather than use a checkbox but I don’t believe it is possible?

Also, how did you get the the Headers to be on double lines?

i.e.
VAT
Included

Here is my table, https://coda.io/d/Untitled_domfr8Pz5pX/_sueOx hopefully you can view it otherwise here is a screenshot just incase. I have left the VAT Rate table there so the actual table still works.

#10

@Juanmata

  1. I don’t believe two-way formulas are possible, but I could be wrong.
  2. To get Headers on double lines, just drag the bottom of the Header row down.
  3. Multiple VAT Periods. I built a test area to illustrate the building blocks that I would use.


#11

Thank you, I’ll give that a go.

Do you know if you can copy and past a formula into a text editor and keep whatever Coda uses?

I’ve been compiling my code in Sublime as I find the areas to small to work with in coda, I’ve been doing ok until I’ve got to the @meh. It will not copy these and it’s making it a struggle to write the code?

Thanks

#12

@Juanmata @alden

Correct, you cannot copy/paste @refs, at least in my experience.

Actually typing “@” is what accesses the back end data structure so you can reference a row directly. Which means pasting “@” does not do this.

Which means after you have taken a long formula that contains lots of @refs into a text editor to work out the logic, that once the formula is ready you can’t just paste it back in Coda. You must paste it and then go into the formula and manually re-enter the @refs using an actual keystroke for “@”.

Lots of opportunity for some cool UX here in the future. :smiley:

That said, the formula UX gets tighter by the week. I find myself using text editors less and less as time goes by. You might consider pushing your comfort zone to stay in Coda when working on formulas. You can create a big fat dummy column for working out logic, and once you’ve got it working you can just paste it into your live column. $.02.

#13

I created a Pay Tracker template a while ago and used dates in a similar way to discern between raises and pay changes. I didn’t want my previous entries to be overridden just because my pay rate changed.

There are a few hidden columns and this template does a few different things. It may apply as a strategy for you and it may not. I also have different “scalers” so overtime could be the typical rate multiplied by “1.5” for example. That could be used similarly for VAT.

If you have questions, let me know and I’ll try to better explain the formulas and strategy. And I know it’s not an apples to apples solutions, but may spark some formula ideas.

2 Likes
#14

OK, so a little playing around, I have the following formula.

IF(
	Date < date(1991,03,19),
		0,
		IF(
			Date >= date(2011,01,04),
				20,
				Lookup(
					[VAT Rates],
						sequence(
							[Start Date],
							[End Date]
					),
				Date
			)
		)
)

So, any date before 1991,03,19 gets a rate of 0%. Any date after 2011,01,04 gets a rate of 20%. Any date inbetween gets the date from the VAT Rates table.

Now although this does work, I’m manually putting the VAT Rate after 2011,01,04 in the formula, same again for the beginning date. I’m also getting an error saying that Sequence is the wrong argument type.

Just wondering if anyone can look at this and push it over the line.