1

I have three products, and each of them has 2 price sets: Retail and Web.

I'm trying to make it easy on the client so that they can just choose "web/retail/deposit" from a dropdown and then "8oz/gallon/25lb bulk" from a dropdown and have the prices calculated automatically.

Right now I have it so that they can choose "retail/web" and it will calculate according to those prices, and the code is working, but I'm struggling to add a third IF for the "deposit", where it would SUBTRACT the cost of the product * quantity. Here's what I have: 

=IF(F13="Web", IF(E13="8oz", (3.19*C13), IF(E13="Gallon", (29.99*C13), 
IF(E13="25lb Bulk", (82.99*C13)))), (IF(F13="Retail", IF(E13="8oz", (2.2*C13), 
IF(E13="Gallon", (29.99*C13), IF(E13="25lb Bulk", (82.99*C13)))))))

How would I go about adding another IF statement depending on a new selection "deposit"?

Cosmic Hawk
  • 225
  • 2
  • 9

1 Answers1

1

I would actually restructure the whole thing into a lookup table as your IF statement is very hard to read and as you have found out, to maintain.

First create lookup table (I've just put it in the same sheet from A23 downwards, but you may want to put it in another sheet)

Lookup       |Type   |Unit       |Factor
Web/8oz      |Web    |8oz        |3.19
Web/Gallon   |Web    |Gallon     |29.99
Web/25lb     |Web    |25lb Bulk  |82.99
Retail/8oz   |Retail |8oz        |2.2
Retail/Gallon|Retail |Gallon     |29.99
Retail/25lb  |Retail |25lb Bulk  |82.99

And replace your IF statement with 

=C13*VLOOKUP(CONCATENATE(F13,"/",E13),A24:D29,4,FALSE)

You can then wrap that in a IF statement if needed.

It also makes it very easy to add new types or unit types without having to put more IF/ELSE in.

Dijkgraaf
  • 11,049
  • 17
  • 42
  • 54
  • This is perfect and will make everything a lot easier in the future. I'm going to create a new hidden sheet with the price/category list and update that as I go along, and use VLOOKUP to populate the info where needed. Thanks! – Cosmic Hawk Dec 23 '15 at 20:14