creating complex formula within excel

  amadas2014 03:02 04 Jan 2014
Locked

Hello all, it's been awhile since I've needed your assistance, but this one is going to be a challenge, I'm sure.

I'm attempting to create a formula within excel to provide me the end result, but the problem is each column or field is going to be completely different.

For example; I need to take a field with a certain entered dimension such as, 24x14x13. In the next column I need the calculated end result but need to add a formula to it to get to that end result.

So I've created a partial formula, =SUM((241413)/1728)*7 where the answer would be 17.69 which would be dumped into the next column.

Problem is, I need the basis of this formula to to work for all the rows with different dimensions underneath. The formula above is unique to that particular dimension. Not one dimension is the same.

so if row one column a houses 24x14x13 row 2 column a would have 19x11x21 row 3 column a would have 18x11x22 row 4 column a would have 22x12x17 and so on, etc, etc.

Basically, I've created a spreadsheet which includes product I ship. Since the majority of courier companies "cube" their packages to obtain a weight. I need a quick reference for the shipping department to refer to rather than measuring each package everytime. Each courier uses their own formula to arrive at a weight. Width x height x length = Y. Y divided by 1728 multiplied by 7 gives the weight they wish to use, rather than actual weight. The spreadsheet includes the dimensions of each package. Therefore I need the formula for each dimension to arrive at the magic number.

I guess I'm seeking a formula within a formula to arrive at a unique number for that specific dimension in that specific row/column. If this makes any sense to you.

Thank you so much for reading and for any assistance you may be able to offer.

Chris

  hastelloy 12:44 04 Jan 2014

Could you not put each dimension in a separate column - 24x14x13 would then have 24 in col a, 14 in col b and 13 in col c. Then in col d =SUM(a:c)/1728*7

  lotvic 15:24 04 Jan 2014

(follow on from marvin42) ... Width in A1. height in B1. length in C1.

Column D Line 1 cell formula should be =SUM(A1xB1xC1)/1728x7 and then you could just drag the formula in column D down the rows. (note I have put x instead of * as pca forum formatting would have made text between 2 asterisks into italics instead of showing the asterisks that in Excel mean 'times')

That is the usual way.

  hastelloy 15:30 04 Jan 2014

lotvic is correct - I thought you were adding the 3 values but, of course you're multiplying!

  wee eddie 15:54 04 Jan 2014

It is essential to give each variable it's own column.

So, with the Formula above, you can leave the SUM and the brackets out, the formula in F1 would now read.

=A1xB1xC1/D1xE1

  lotvic 16:09 04 Jan 2014

wee eddie is correct. As an example, using your 24x14x13 /1728 x7

D1 should contain 1728 and E1 should contain 7 and F1 is for the answer

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Best phone camera 2017

Stunning new film posters by Hattie Stewart, Joe Cruz & more

iPad Pro 10.5in (2017) review

28 astuces pour profiter au mieux de votre iPhone