calculate with totals in a schedule

[HVAC-Novice]

How can I calculate with Grand totals in a schedule?

I have the below example of a space lighting power density schedule. I have the power density, load, and area for each space. and I have the total lighting load and total area. and what I want is also have the power density for the entire building.  But I don't see a way to use the calculated totals at the bottom of columns in an equation. I also can't just use the average of all space power densities since they would have to be weighed against total area (and there seems to be no obvious average function in totals anyway). In my example I have to divide total actual load by total area and should come up with 0.67 VA/ft².  How do I automate that in Revit? 

[barthbradley]

Can't you get there with Percentage Calculated Value?

[ToanDN]

Share a sample file that has the same schedule with a few rows.

[HVAC-Novice]

that gets me somewhere closer. I can calculate the % each row is compare to total. Based on that i could calculate the total load and area and show it in each row. and last column shows my total building power density.  percentage each row is compared to the total. I can hide the 4 columns with interim calculated values. but the last column still will look weird. it gives me the value I want, but looks odd to show that value in each row. 

I tried to show the Maximum/minimum of that column hoping that gives me the number at the bottom of the schedule, but i couldn't figure out how to show that minimum/maximum. Also not sure if that really helps me here. 

Here how the schedule looks like after hiding interim calculation columns:

[HVAC-Novice]

File of current project is too large to attach here. 

It also may not work for you since some of the parameters in the schedule are generated by my lighting plugin "elum tools". 

[ToanDN]

So you want to show the average % at the bottom of the % column?

[HVAC-Novice]

Correct. Ideally I don't show the last column with the same value in each row at all. But that value it calculates (0.67) could be at the bottom where the actual power density for each space is listed. Something like this:

what I have now "works". But it is a bit confusing since the value that really belongs to the entire building is in each row (implying it is for each space)

[ToanDN]

Here is what I can do:

[HVAC-Novice]

I think I know what you did. I tried something similar weigh each space's power density. the show the fraction in the last column and add them up. Problem is, I still have that last column and those values may even be more confusing and the calculated total is incorrect, probably due to rounding. Manually calculated it is 67.2 W/ft². but now I get 0.695. So almost 0.03 more. Actually when I change the decimal points of each column my original result changes to 0.669 (0.672 is actual value). Still close enough since rounding both values is 0.67. 

So my original idea still may be better. Still the problem with showing a somewhat not needed column, but more accuracy. 

Correction: I had accidentally calculated with the power density %, not with area %. Now the calculated values are correct (0..669). so this method works from an accuracy point. But I still show a somewhat confusing column. 

[barthbradley]

---

@HVAC-Novice wrote:

Now the calculated values are correct (0..669). so this method works from an accuracy point. But I still show a somewhat confusing column. 

---

So, Percentage Calculated Parameter works, but you don't like the schedule formatting? Is that about the size of it? How would you like to see it formatted - in an ideal world?    

[ToanDN]

[HVAC-Novice]

The 2 last columns show my original method, and your method. At minimum I have to decide which one to use. In an ideal World I would hide both (mine and yours) and show the calculated value under the column of the Actual power density (where the arrow moves it to). That probably is not possible with oob tools, unless someone comes up with an yet unknown trick. 

If nothing else, I at least got a somewhat usable solution and learned some new methods. That alone is worth the journey 🙂

[barthbradley]

Ever consider overlaying differently formatted duplicate schedules on the sheets? 

[HVAC-Novice]

I didn't really consider that. But the following potential problems come to mind:

- clutter up my browser with addl. schedules

- requires me to place 2 schedules, and align them

- Keep them aligned if I move schedules, or adjust column width

It goes against a bit against the Revit idea to automate things and make it more fool-proof. In this example, keeping one (somewhat useless and potentially confusing) column visible seems to be the lesser evil. At least the look would be consistent, even if I move things around. I still have to sleep over this to decide which of columns to show. 

[barthbradley]

When life gives you lemons, make lemonade...or hard cider. 😉