IFS statement not doing what I thought it would do

Jaros · 06-18-2021 01:36 AM

Hi, I want to have a simple table for a QSR where a user inputs the amount of sales generated from each third party delivery company and it calculates the actual turnover generated after taking in to account what needs to be be paid to the delivery companies.

So I inputted the following

IFS([Delivery Companies]="Uber Eats",[Deliveries]*0.3*1.15,
		[Delivery Companies]="Mr D",[Deliveries]*0.225*1.15,
    [Delivery Companies]="Buzz",[Deliveries]*0.225*1.15)

The [Deliveries] column is where the daily sales amount is inputted and I want to calculate the commission which is the 0.3, 0.225, and 0.225.

The other value, 1.15 is to calculate the VAT on top of that.

I tried to input the value as both a FORMULA and an INITIAL VALUE and while I get no errors in the expression assistant I am just getting a “$0.00” result in that column. Where am I going wrong?

Jaros

Just seen that the multiplication signs have been removed from my post.

Can you not use multiplications in Appsheet?

Jaros

I want to multiply [Deliveries] by 0.3 and 1.15

Jaros

Got caught out by my dereference again.

So solved it.

Just don’t know why it works in testing if it won’t work when I do it

Steve

A virtual column’s App formula expression will be reevaluated whenever the row is updated and whenever the app syncs, but a virtual column value will not be stored in your spreadsheet.

A regular (non-virtual) column’s expressions are only updated when the row is updated, so you’ll need to open existing rows in a form and re-save each one to update the column values with the new expression. New rows will get the new values automatically.

Jaros

Thanks Steve

Jaros

I am still getting an error here and I am not sure why.

I changed the expression to reflect the Ref codes so instead of having three Names, Uber, Mr D and Buzz, I have DEL001,DEL002,DEL003.

IFS([Delivery Companies]=“DEL001”,[Deliveries]0.31.15,[Delivery Companies]=“DEL002”,[Deliveries]0.2251.15),([Delivery Companies]=“DEL003”,[Deliveries]0.181.15)

The first two parts are working and it does the Calculation. The third one Buzz does not work. I thought it had something to do with perhaps it not be the right file type as you get errors when you have enums with the incorrect base but the type in my delivery company table is text.

What else could possibly be causing this?

As before there are multiplication signs between the figures.

Aurelien

Hi
I copied and indented the exact expression you provided (a good practice you may want to adopt with this kind of sequential expression).

IFS(
  [Delivery Companies]=“DEL001”,[Deliveries] *0.3* 1.15,
  [Delivery Companies]=“DEL002”,[Deliveries] *0.225* 1.15),
  ([Delivery Companies]=“DEL003”,[Deliveries] *0.18* 1.15)

It seems you have too many parenthesis
You should have written:

IFS(
  [Delivery Companies]=“DEL001”,[Deliveries] *0.3* 1.15,
  [Delivery Companies]=“DEL002”,[Deliveries] *0.225* 1.15,
  [Delivery Companies]=“DEL003”,[Deliveries] *0.18* 1.15
)

Or, equivalent (I changed the last input, so that will work whatever is the value of [Delivery Companies]😞

IFS(
  [Delivery Companies]=“DEL001”,[Deliveries] *0.3* 1.15,
  [Delivery Companies]=“DEL002”,[Deliveries] *0.225* 1.15,
  true,[Deliveries] *0.18* 1.15
)

Jaros

Thanks, appreciate the assistance.

I will indent in future. Can see how that helps.