I previously posted a similar question; however, I did not provide all the details and intended to adapt any response to my need. I was UNSUCESSFUL, being 'to smart by half'.
The following table represents real estate type and their statuses, with their quantity and occupancy. The 'Total' rows (6,10,15,20 - in the sample below) represent the weighted average in the percentage column.
I want to create a formula in column E that allows me to copy/paste new data in columns A, B, C, D and columns E will adjust to the new quantity of the data sets (with the format and columns A, B, C, D being the same). For example, sometimes the quantity of multi-family data set has 2, 4, 5 rows rather than 3 rows in this example below. The goes with 'office' and sometimes there is 'retail (or other real estate types).
As such, I am trying for a formula that is already in Column E (adjusting to the change in Column A-D data) and I can drag the formula down if there are more rows in the data set. Also, I am validating the weighted average formula is calculating correctly and not relying on the data totals in columns C and D that are provided to me.
My current formula (before considering your assistance) is the following, but it is not dynamic to grow/contract with the quantity (row) changes of MF, Office, etc. and I go into the formula and add/reduce rows in the formulas on each of the total rows in column E.
| E6 | =IF(ABS(SUMPRODUCT(C3:C5,D3:D5)/C6-D6)>0.01,"FAIL","VALID") |
Then, I started with a formula that only displays calculations in Column E based on an if statement, but cannot figure out the how to make a formula that runs the calculation based on the rows between the two 'blank' cells of column B, which is the only consistent pattern I can think of to base the dynamic calculations and make it drag-able down column E if there is more data provided.
| E6 | =IF(OR(AND(ISTEXT($A6),$B6=""),AND($A6="",$B6="")),"", ??????????????????? |
A | B | C | D | E | |
1 | Type | Status | Amount | % | Valid/Fail |
| 2 | Commercial RE | ||||
3 | MF-1 | Good | 20 | 95.0% | |
4 | MF-2 | Good | 40 | 88.0% | |
5 | MF-3 | Good | 30 | 92.0% | |
6 | Total Good MF | 90 | 90.9% | VALID | |
7 | MF-1 | Bad | 20 | 82.0% | |
8 | MF-2 | Bad | 10 | 84.0% | |
9 | MF-3 | Bad | 10 | 73.0% | |
10 | Total Bad MF | 40 | 80.3% | VALID | |
11 | Office-1 | Good | 50 | 94.0% | |
12 | Office-2 | Good | 10 | 94.0% | |
13 | Office-3 | Good | 30 | 91.0% | |
14 | Office-4 | Good | 40 | 92.0% | |
15 | Total Good Office | 130 | 92.7% | VALID | |
16 | Office-1 | Bad | 10 | 71.0% | |
17 | Office-2 | Bad | 10 | 83.0% | |
| 18 | Office-3 | Bad | 20 | 78.0% | |
| 19 | Office-4 | Bad | 50 | 77.0% | |
| 20 | Total Bad Office | 90 | 77.2% | VALID |
If you can help figure this formula out, I would greatly appreciate it. This forum has always been an amazing resource. Thank you.
