Excel: Method for Calculating Averages

I'm working on creating the table below in Excel and I'm trying to find the best way to calculate the Size (944) / Rent (2,212) / Rent PSF ($2.34) values so that I don't need to modify these formulas every time I add a new Unit Mix type.

Right now, my formula for calculating the average Size looks like so:

=((F3*E3)+(F4*E4)+(F5*E5)+(F6*E6))/E8

This is summing up Count * Size for each unit mix divided by the total unit count.

You can see how if I add another unit mix to the bunch, I'll have to modify this formula.

Is there any way around that?

doremi

Posted 2016-06-07T17:30:56.147

Reputation: 279

Your formula doesn't make much sense without the actual columns and row numbers shown as well. It seems like you're referring to the Market Residential row but I'm not sure. – Sun – 2016-06-07T17:48:55.117

Answers

You could use SUMPRODUCT():

=SUMPRODUCT(E3:E6*F3:F6)/E8

This will iterate through the ranges and multiply each row and sum the results. Then it divides the total by E8.

If what is wanted is a completely dynamic formula then use this:

=SUMPRODUCT(D3:INDEX(D:D,MATCH("Market Residential",A:A,1)-1)*E3:INDEX(E:E,MATCH("Market Residential",A:A,1)-1))/INDEX(D:D,MATCH("Market Residential",A:A,1))

It will find the row that has Market Residential in Column A and use that as a reference. This way as rows are added or removed the formula will always capture the correct ranges.

Though the first as long as it is not deleting the first or the last will automatically change the range size with additions or deletions also.

Scott Craner

Posted 2016-06-07T17:30:56.147

Reputation: 16 128

I think the issue is if something is added in row 7, the formula will need to be updated. – Raystafarian – 2016-06-07T17:38:45.300

Fair enough, I read that they will not need to continue to add another +(F7*E7) each time. – Scott Craner – 2016-06-07T17:39:59.377

I had to read it twice, I thought it meant updating numbers in E at first. – Raystafarian – 2016-06-07T17:40:50.640

@Raystafarian see edit. The first should grow and compact with addition and deletion of rows as long as the addition is inside the range and the deletion does not include the first or last row in the data set. – Scott Craner – 2016-06-07T17:47:41.777

You can just use an array formula for SUM

=SUM(E3:E6*F3:F6)

It's an array formula so after typing it you must hit Ctrl Shft Entr

That should update if you make a change to the middle of the range.

Raystafarian

Posted 2016-06-07T17:30:56.147

Reputation: 20 384

What is the difference between SUM and SUMPRODUCT? – doremi – 2016-06-07T21:34:30.477