Formula that adds two separate cells if a third cell has a certain value into another cell

I'm working on a spreadsheet that includes items I sell as well as the packaging it goes in.

The formula I'm looking for basically will tell a cell that if one cell (# of box) is equal a certain # it will add two separate cells by the weight of the box.

I have cells for each of the following: Weight in lb and weight in oz of the item (no box), a cell which holds the number of the box (ranging from 1-15) and two cells with weight in lb and weight in oz which would be the total weight of the item + box.

So, for example, if weight of item equals 1 and box cell equals 2 then it will put the weight of item + weight of box into a third cell.

Here's a picture of my current spreadsheet, still being worked on if it helps.

http://i.imgur.com/qGnldj8.png

Basically, I would like to do something such as if P3 = 5, then Q3 will equal N3 + U6 and R3 will equal O3 + V6.

Originally Posted by simarui

ah, thanks hoyasaxa, I didn't catch that package # might tie to either box or envelopes.

here's one way to do it without merging the datasets...

does it bother you that the oz column can return a number higher than 16?

I kept them separated because one column is for boxes while the other is for envelopes. Technically, it does bother that they can equal mover 16 in the oz column because anything over becomes a pound, but I'm sure I could just do the math by hand for those occurrences unless there's a way to make the formula also have oz to lb conversion but that would include adding that value to the other cell.

I'm looking at the formula you made but it doesn't seem to do the math right.
For example, B12 is 1.50 with Packabe #11 / C12. So E12 should equal 1.50 + 1.20 (M5, Package #11) = 2.7 but comes out as 2.19.

I've also made a quick edit to the table, moved an envelope row that was under the box column to the side.

On the weight columns for Boxes the left one is pounds while the right one is ounces, that is why I have two columns under weights for everything (if that info helps), while envelopes only weight in ounces.

Thanks Zyner,

I'm still not sure what we're trying to accomplish. Which cells have the desired output and what should the desired output look like? It looks like you have 11 packages each with their own "package number", but only 8 boxes. How do I know which box is used for which package?

also, am I correct in assuming you want to see the lbs in column D and the ounces in column E? or do you want those two numbers together in the same cell?

I have 7 different box types and 8 different envelope types, each with their own corresponding number.
My desired output is for the left column (pounds/lb) to equal the weight in pounds of the item + the weight in pounds of the box while the right column has the weight of the item in ounces + the weight of the box/envelope in ounces. This two columns are under Weight w/ box (lb & oz), where as the previous weight columns are of the item alone.
I will be inputting which box/envelope number goes into column C/Package #, what I would like is if there could be a formula that will change its calculations based on what number is on that column.

So I suppose what I'm trying to say is based on whatever number is on column C it will give a calculation that's appropriate to that number into columns D & E. The numbers that are currently on that I'm sharing with you are just to test with and will be changed (the numbers on column C).

I have pounds and ounces separated due to some ounces coming with numbers after their decimal point. If you can find a way to automatically calculate into one cell for pounds & ounces to be combined as well as conversion from ounces to pounds if required that would help out a lot; I kept them separated because I wouldn't make a formula that would add them because it would input 1.0 lb + 2.5 oz and equal 3.5 lb.

It does at least seem a step in the right direction so I thank you for the effort in helping me thus far.

thanks again hoyasaxa,

Zyner,

the attached incorporates hoyasaxa's fix above and uses some additional criteria to force oz over 16 into the lbs column.

I'm sure I can put it all in one column if you want it, but this is already done if it's all the same to you.