Hi everyone -- thanks to Eric W for a solution he posted for another member! Is it possible to add a third result - the variance $'s in column 'AG'? Thank you....
| BP_Model_Master_NEW.xlsx | ||||||||||||||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| R | S | T | U | V | W | X | Y | Z | AA | AB | AC | AD | AE | AF | AG | AV | AW | AX | AY | AZ | BA | |||||||||||||||||
| 7 | Description | Cost Type | Item | Quantity | Unit | Unit Cost | Budget Estimate | Comps/Notes | t12 | Quantity - New | Unit | Unit Cost - New | Budget Estimate - New | Comps/Notes | Quantity: New vs. Original (higher/lower) | Cost Estimate: New vs. Original (higher/(lower) | t15 | Description | Budget Estimate | |||||||||||||||||||
| 8 | Lot Construction | Earthwork | 685.00 | Per Lot | $9,051.44 | $6,200,237.00 | 685.00 | Per Lot | $9,051 | $6,200,237 | $0 | $0 | Lot Construction | $25,538,087 | ||||||||||||||||||||||||
| 9 | Lot Construction | Utilities & Roadway | 22,695.00 | LF | $839.74 | $19,057,850.00 | 22,695.00 | LF | $840 | $19,057,850 | $0 | $0 | Land Acquisitions | $7,300,000 | ||||||||||||||||||||||||
PS1 | ||||||||||||||||||||||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| BA7 | BA7 | =+tblExpenseInputNew[[#Headers],[Budget Estimate]] |
| AF8:AF9 | AF8 | =+[@[Quantity - New]]-[@Quantity] |
| AG8:AG9 | AG8 | =+[@[Budget Estimate - New]]-[@[Budget Estimate]] |
| AZ8:BA28 | AZ8 | =LET(r,UNIQUE(tblExpenseInputNew[Description]),SORT(CHOOSE({1,2},r,SUMIFS(tblExpenseInputNew[Budget Estimate],tblExpenseInputNew[Description],r)),2,-1)) |
| X8:X9 | X8 | =+[@[Unit Cost]]*[@Quantity] |
| AD8:AD9 | AD8 | =+[@[Unit Cost - New]]*[@[Quantity - New]] |
| Dynamic array formulas. | ||
| Cells with Conditional Formatting | ||||
|---|---|---|---|---|
| Cell | Condition | Cell Format | Stop If True | |
| S8:S71 | Expression | =COUNTIFS( XLOOKUP( R8, Level2Choice, Level3Result )#, S8)=0 | text | NO |
| R8:R71 | Expression | =COUNTIFS( XLOOKUP(Q8, Level1Choice, Level2Result )#, R8)=0 | text | NO |
| Cells with Data Validation | ||
|---|---|---|
| Cell | Allow | Criteria |
| V8:V9 | List | =INDIRECT("tblUnit[Unit]") |
| AB8:AB9 | List | =INDIRECT("tblUnit[Unit]") |
| R8:R71 | List | =XLOOKUP(Q8, Level1Choice, Level2Result )# |
| S8:S71 | List | =XLOOKUP(R8, Level2Choice, Level3Result )# |
