Average density formula for different quantities of materials

Question

Greetings!

What I'm attempting to do in Excel is calculate average densities of alloys while knowing the densities of each metal component and the mass of each component.

I currently have it set up to calculate average density with up to 6 components and the formula looks like, where B values are masses and C values are respective densities:

=SUM(B2:B7)/((B2/C2)+(B3/C3)+(B4/C4)+(B5/C5)+(B6/C6)+(B7/C7))

It works fine if I have values for all 12 cells (for calculating average density of a 6 component alloy) but I'd like it to work if I only enter data for 2 rows, or only 3 rows, etc. (ie- calculate the proper result despite #DIV/0 errors if any of the later rows are empty)

Suggestions?

Answer 1

This only works properly when if you have unused entry in C you also have no value entered in B on the same row.

=SUM(B2:B7)/(IFERROR((B2/C2),0)+IFERROR((B3/C3),0)+IFERROR((B4/C4),0)+IFERROR((B5/C5),0)+IFERROR((B6/C6),0)+IFERROR((B7/C7),0))

Each one of the IFERROR() statements says do your divide by math and if it doesn't create an error, use that value, but if it results in an error (presumed to be #DIV/0!) then just return 0 for that part of the whole formula.

Answer 2

Hoss wrote:

In any case, if a component doesn't have a corresponding density, then you shouldn't be entering a mass on that row, either.  (The solutions given so far (mine and joeu2004's) will give an average density assuming an infinite density for that component if you do make that mistake.)

Speak for yourself!  While entering data (and for other reasons as well), density might be empty while mass is not.  I explicitly address that issue with the second alternative that I offered.  I wrote:  "the latter alternative returns the correct average density when only mass is entered in some rows in B2:B7 and the corresponding density is empty (effectively zero)."

Answer 3

joeu2004's reply pointed out one caveat with my solution:  In order to work, all the density cells (e.g. C2) must have a value so that the reciprocal (1/C2) does not return a #DIV/0! error (say if you wanted to allow for 10 components, but for a specific alloy there are only 3 components).  Two ways to fix this:

1)  Enter anything for the density for the remaining rows.  Because there is no mass associated with the density, it will not affect the average density result.

2)  Adjust the reciprocal formula:  D2=IFERROR(1/C2,0).

In any case, if a component doesn't have a corresponding density, then you shouldn't be entering a mass on that row, either.  (The solutions given so far (mine and joeu2004's) will give an average density assuming an infinite density for that component if you do make that mistake.)

Answer 4

JVTrain wrote:

What I'm attempting to do in Excel is calculate average densities of alloys [...].  I currently have it set up to calculate average density with up to 6 components and the formula looks like, where B values are masses and C values are respective densities:

=SUM(B2:B7)/((B2/C2)+(B3/C3)+(B4/C4)+(B5/C5)+(B6/C6)+(B7/C7))

It works fine if I have values for all 12 cells (for calculating average density of a 6 component alloy) but I'd like it to work if I only enter data for 2 rows, or only 3 rows, etc. (ie- calculate the proper result despite #DIV/0 errors if any of the later rows are empty)

If you are truly using Excel 2003, as your original posting indicates, or you require Excel 2003 compatibility and, in either case, you cannot use IFERROR, you could do the following:

=IF(SUMPRODUCT(B2:B7,C2:C7)=0,0,SUM(B2:B7)/(IF(C2=0,0,B2/C2)+IF(C3=0,0,B3/C3)+IF(C4=0,0,B4/C4)+IF(C5=0,0,B5/C5)+IF(C6=0,0,B6/C6)+IF(C7=0,0,B7/C7)))

The IF(SUMPRODUCT(B2:B7,C2:C7)=0,0,...) is needed in case all of C2:C7 are zero.  Using that test instead of the more obvious IF(SUM(C2:C7)=0,0,...) ensures that you have at least one paired mass and density input in columns B and C.  Thus, the formula continues to return 0 while you enter values into columns B and C in any order.

However, that formula is somewhat unwieldy and even more so if you ever want to increase the number of alloys.

Alternatively, try the following formula, which must be array-entered (press ctrl+shift+Enter instead of just Enter):

=IF(SUMPRODUCT(B2:B7,C2:C7)=0,0,SUM(IF(C2:C7<>0,B2:B7))/SUM(IF(C2:C7<>0,B2:B7/C2:C7)))

When entered correctly (ctrl+shift+Enter), you will see curly braces around the entire formula.  We cannot type the curly braces ourselves.  That is just how Excel indicates an array-entered formula.

The latter alternative also corrects a technical flaw in the form of your formula, repeated in the first alternative above, namely:  the latter alternative returns the correct average density when only mass is entered in some rows in B2:B7 and the corresponding density is empty (effectively zero).

Answer 5

You didn't specify, but I assume column B has component mass (say, in grams) and column C has component density (say, in grams per cubic centimeter).

I think using a helper column is the simplest solution:

In column D, place the inverse of the value in column C, e.g.,  D2=1/C2

Then your average density =sum(b2:b7)/sumproduct(b2:b7,d2:d7)

As long as at least one row has a value in both columns B and C (and assuming all values entered are non-negative) this formula will work.

There's probably a way to do it with an array formula, but my first attempt was unsuccessful, so I gave up on that.

(On a scientific note:  your formula is valid only if the volume taken up by the alloy is the sum of the volumes taken up by the components individually, e.g., that the atoms of 1 metal do not fill in the empty space between the atoms of the other metal(s).  I'm not sure this is generally true.)