Categories in Data Tables?

[alohafromHI]

Hi everyone,

So for the sake of an example, say I want to calculate a prices per pound, but there are different colored containers that indicate different prices. Like a red container is $1/pound, a green container is $2/pound and a blue container is $3/pound. So my Data Table would look something like below (w/o equations):

[TABLE="width: 500"]
<tbody>[TR]
[TD]Color[/TD]
[TD]Weight (lbs.)[/TD]
[TD]Price[/TD]
[/TR]
[TR]
[TD]Red[/TD]
[TD]1[/TD]
[TD]=[@weight]*1 = $1[/TD]
[/TR]
[TR]
[TD]Green[/TD]
[TD]1[/TD]
[TD]=[@weight]*2 = $2[/TD]
[/TR]
[TR]
[TD]Blue[/TD]
[TD]1[/TD]
[TD]=[@weight]*3 = $3[/TD]
[/TR]
</tbody>[/TABLE]

So if someone is buying 1 pound of something, the price would be different depending on which color container it is in: $1 in red container, $2 in green, and $3 in blue. But since the formulas for each container are different, Data Tables gets messed up (so the table above isn't actually possible). Is there a way that I can somehow set a category for the container color that would dictate which equation Excel would use based on the category I select? Or would I just need to make a separate data table for the red containers, one for the blue, and one for the green?

Thanks

 

[AliGW]

I'm not quite clear what you are asking for, but it seems to me that all you really need is a simple table like this:

Excel 2016 (Windows) 32 bit
	A	B
1	Red	1
2	Green	2
3	Blue	3
Sheet1

Then your formula will be something like this:

Excel 2016 (Windows) 32 bit
	A	B	C
5	Green	12	24
Sheet1

Cell Formulas
Range	Formula
C5	=B5*VLOOKUP(A5,$A$1:$B$3,2)

 

[alohafromHI]

Sorry, I didn't really make it clear...

But the VLOOKUP function, from what I understand, is to look up items and values that already exist in an extensive table. In this case, I want to be able to input the color and weight of the container and I want excel to auto calculate the price and I want to keep adding to the table as each container is sold. But because the price per weight of the different colored containers are different, I want to figure out if I could somehow set different formulas to calculate prices based off which color container I choose.

I was just wondering for efficiency's sake because like if I set the red container to be $1.75/pound and someone comes and buys 4.72 pounds of something, I want to just be able to input "red container" and "4.72 pounds". Then excel would auto calculate 4.72*1.75 rather than me having to input the 1.75 every single time someone comes with a red container. Sorry, I feel like I'm just making this more confusing. Hope that helped at least a little

I'm not quite clear what you are asking for, but it seems to me that all you really need is a simple table like this:

Excel 2016 (Windows) 32 bit
	A	B
1	Red	1
2	Green	2
3	Blue	3
Sheet1

Then your formula will be something like this:

Excel 2016 (Windows) 32 bit
	A	B	C
5	Green	12	24
Sheet1

Cell Formulas
Range	Formula
C5	=B5*VLOOKUP(A5,$A$1:$B$3,2)

 

[AliGW]

That's what my suggested solution is doing. In A5 you input the colour of the container (you could have a drop-down list to select from, if you wanted to). In B5, you input the number you need multiplying. The formula in C5 then looks up the value of your chosen colour in the lookup table and multiplies it by the number in B5 - your £1.75 goes in the lookup table next to the cell that says "Red". From what you have said so far, I cannot see how this does not fit the brief, so if it doesn't, you are going to need to make your requirements clearer.

 

[alohafromHI]

Ohh I see what you're saying now. My bad I got a little confused, but I understand now. Thanks!

 

[AliGW]

You're very welcome!