1

I'm looking how to to do a math operations for specific row depend on the row the switch formula append.

The challenge
  1. I have a table with product, their product price and a shipment price.
  2. The shipment price depend on the product. So the calculation must be a specific part of the product price.
  3. Also I have a lot of products and the calculation of the shipment is changed frequently. I'm trying to enter the formula at only one place, to be able to maintarate it easier in the future.
How the table looks like
1 A Product B Product price C Shipment price
2 Product-1 5
My formula is here
3 Product-2 10
4 Product-3 15
My formula
=SWITCH (
A2:A,
"Product-1", B2:B*2.0,
"Product-2", B2:B*3.0,
"Product-3", B2:B*3.0,
0
)
The issue

Insted to get the shipment price for each row I get it only for the first row (B2) and it is applied for the whole column (C). enter image description here


TL;DR

I'm new to Google sheets formulas. So far I was able to find everything that I need in ther documentation but I'm stuck to one issue. I tried to find a solution but sinse I'm new to it I'm not sure with what keywords to search. Maybe it's something very simple but please don't judge me.

Also the example is simplified, do not look for logic in the fact that the Shipment price is determined according to the product.

1
0

Use arrayformula(ifs()), like this:

=arrayformula( 
  ifs( 
    A2:A = "Product-1", 2.00 * B2:B, 
    A2:A = "Product-2", 3.00 * B2:B, 
    A2:A = "Product-3", 3.00 * B2:B, 
    true, iferror(1/0) 
  ) 
)

To do this in a more structured manner, create a lookup table and use arrayformula(vlookup()).

2
  • What is the reason why the SWITCH can't work? I'm looking to understand more behind the Google formula syntacts and what are the limitations. Nov 25 at 11:35
  • The behavior of switch() and isbetween() changed a couple of months ago. Array arguments are accepted, but the dimensions of all arguments must be the same. ifs() is simpler to get right in this use case. Nov 25 at 11:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.