Search code examples
google-sheetsformattingrounding

Google Sheets complex formula using many references

I have an export sheet where I get all products with a couple of details like price, special price etc.

I have formatted certain cells to create simple tables for someone to fill in or adjust the prices more easily. Then, those tables are used to update the prices in a 'copy' of the export, but with many conditions to automate it as much as possible.

So the goal is to have the prices being taken from the formatted/simple sheet if updated. This is already working perfectly, but now it should consider rounding the prices to .95 or .49 when it is a product on a specific shop. To do so, it should first look up the VAT and recalculate the ex. VAT price to include VAT, then round it as stated above, then recalculate the price to get the ex. VAT for the rounded price.

This is what I got so far:

=IF($G2="drankuwelnlview",IF(ISNA(VLOOKUP($A2,'HG Productenlijst'!A2:J,10,FALSE)),VALUE(Importdata!C2),IF(VLOOKUP($A2,'HG Productenlijst’!A2:J,10,FALSE)="BTW 21%”,VALUE(Importdata!C2)*1.21,IF(VLOOKUP($A2,'HG Productenlijst'!A2:J,10,FALSE)="BTW 9%”,VALUE(Importdata!C2)*1.09,
VALUE(Importdata!C2)))),VALUE(Importdata!C2))

In this case, it should first check to see if the product is for the 'drankuwelnlview' store in column G. If so, it should take the SKU value in column A and search in the 'HG Productenlijst' tab for the same SKU (also in column A) and get the value in column J, which is a string containing the VAT like: 'BTW 21%', 'BTW 9%' or 'BTW 0%'.

It should extract the percentage and multiply or add the VAT to the number/price and then round up to either .95 or .49. After that, it should divide or remove the VAT again so the price is back to ex. VAT.

The tab 'HG Productenlijst' is where the VAT is, and 'Importdata' is an already formatted table with the updated product prices and details.

In addition, here is an example of the table in 'HG Productenlijst': enter image description here

Here is an example of the table in 'Importdata': enter image description here

The 'Copy of Importdata' is the sheet where we want to update the prices to be nicely rounded after determining the VAT for the calculation.

Solution

  • I don't know if I fully understood what you pretended with the roundings, but try with this formula. It uses LET to make steps and create variables to those names. Try it and let me know if it's useful!

    =IF($G2="drankuwelnlview",
LET(vat,IFNA(VALUE(REGEXEXTRACT(VLOOKUP($A2,'HG Productenlijst'!A2:J,10,FALSE),"\d+"))/100,0),
vatpr,VALUE(Importdata!C2)*(1+vat),
upvalue,IF(ISBETWEEN(MOD(vatpr*100,100),0,50,0,0),ROUNDUP(vatpr)-0.51,ROUNDUP(vatpr)-0.05),
upvalue/(1+vat)),
VALUE(Importdata!C2))

    It finds the VAT with REGEXEXTRACT (so you don't need many IF statements), and then calculates the price with the VAT. Rounds it up and finds if the remaining numbers after the comma are between 0 and 50 so it then rounds it to .95 or .49. And finally returns that value divided (1+vat)