Round Up Calculated Total of Revit Schedule Cell

[rvtquestions]

I need help rounding up the Calculated Total of a cell within a column of a Revit Schedule. As an example, I have a schedule with (4) parameters: "Count", "Value","Denom","Result". Count being the number of elements, Value being some value associated to the element, Denom being the Denominator Value is to be divided by, and Result being the Result. All columns shown in grey have the "Calculate Totals checked" and thus show the combined total for each type.

 

For column "Result" it is a calculated value as a "Number" with a formula "Value/Denom" at a Field Format of 2 decimal places.

My issue is rounding up these values to the nearest whole number. I cannot simply change the "Result" calculated value to "roundup(Value/Denom)" as it would result incorrectly or even null

for example, the first row of Result of type count 5 is really the sum of (3/24 = 0.13) 5 times or 0.65.

0.13

0.13

0.13

0.13

0.13

if I used the roundup function it would result in, resulting in a total of 5.

1

1

1

1

1

The desired outcome being the next whole number of 0.65 = 1.

Any idea how to go about this? Thank you.

[barthbradley]

How about changing precision of value returned through the FORMAT tab? 

[rvtquestions]

Hey @barthbradley

I already have "Result" set to a Field Format of 2 decimal places. if I change it to 0 or higher it will return odd values but most likely 0.

[ToanDN]

Can you share the file you are working on so that we don't have to recreate it?

[barthbradley]

Wouldn't you need another calculated field to do the rounding of the exact total value returned in Results. In other words, no roundup in the Value/Denom calculated parameter.  Or maybe I'm misreading what you want to do.  

[barthbradley]

[rvtquestions]

Hey @barthbradley I appreciate your help. Thank you for your response. From the looks of what you have it seems to be right. Just so we are on the same page, in your example there are (5) elements that each have a "Value" parameter of "3"? If you checked Itemize all instances, what does it show in Result for each cell? It should be 0.125. Not sure about RoundupResults that is what I am trying to avoid. To reiterate (if itemize all instances was checked) it should be (5) rows of:

[Value][Denom][Result]

[    3    ][    24    ][  0.125 ]

which adds up to 0.625, which I'd like to round to the next whole number being 1.

So if instead value was 7, each result would end up being 0.30 depending on the rounding and add up to 1.5 and result as 2.

[barthbradley]

Or do you mean like this?

[rvtquestions]

Looks promising so far the first Desired Result is what I am aiming for. Not sure if this makes a difference but the numbers I end up with aren't always as neat as .125 or .25 they can vary from 0.008 to 0.1, etc. But anyways yes Desired Result #1 is what I am after.

[barthbradley]

Well, you can play with values, but the "Desired Results" column is achieved by formatting Parameter to change its Field Format Units (Fixed and 0) and Calculate Totals.  No roundup in formula. Just Value/Denom.  

[rvtquestions]

Hi @barthbradley

Yes I've tried playing around with the tolerances, from Setting multiple decimal places to 0 to even nearest 10th. The closest solution is in fact 0 decimal places; however, the tricky situation as I will reiterate is that I would like to roundup the total result, not just round. Difference being if the sum result was 0.4 I would still like it to be 1 and not 0.

My current thought is to maybe have one raw Result column, one Result column that is rounded, and one Result column be the difference between the two with a condition:

(if r2-r1<0.5, some added factor, else r2)

[   r1  ][  r2 ][  r3  ]

[0.44][  0  ][   1    ]

[Anonymous]

You will need to create two different calculated columns to do some math...

1) First go to the Formatting tab for the Result column and change the drop down under Field formatting: to Calculated totals

2) create a calculated parameter (result%) that is the percentage of the results column by the <Grand total>, again go to the formatting tab and under the Field Format... change Units: to "General" .

3)create a calculated parameter Type: Number and under Formula: roundup(result%). this should then give you the rounded up number of the grand total of your base result column.

you can then hide the first result column and the % column. and un-check itemize every instance and get the following...

[Anonymous]

edit formula...

3)create a calculated parameter Type: Number and under Formula: roundup(Result/result%)

[rvtquestions]

@Anonymous

Thank you for your reply and great workaround. However, I believe this would only work for a single grand total right? In your example it assumes there is only one type that is being calculated. It would need to work off the total per combined cell. For example if there was another type added to the schedule say:

[ Result ][ Result% ][ Result (Round Up) ]

TYPE 1

[    0.16  ][ 0.3333    ][ 1                                ]

[    0.16  ][ 0.3333    ][ 1                                ]

[    0.16  ][ 0.3333    ][ 1                                ]

TYPE 2

[    1.25  ][ 0.5          ][ 3  (should be 2)       ]

[    1.25  ][ 0.5          ][ 3  (should be 2)       ]

 I would need the 0.16 type to work independently of the 0.25 type. In your example it would turn out like this:

[ Result ][ Result% ][ Result (Round Up) ]

TYPE 1

[    0.16  ][ 0.0537    ][ 3 (should be 1)       ]

[    0.16  ][ 0.0537    ][ 3 (should be 1)       ]

[    0.16  ][ 0.0537    ][ 3 (should be 1)       ]

TYPE 2

[    1.25  ][ 0.419          ][ 3 (should be 2)       ]

[    1.25  ][ 0.419          ][ 3 (should be 2)       ]

[Anonymous]

It should still work as long as you have your Sorting/Grouping established correctly. Could you send me your file so I can try implementing it?

[rvtquestions]

Unfortunately not near the actual document at the moment and working hypothetically. However, in your example schedule a family with different types such as Type 1 and Type 2. Type 1 holding a value parameter, ideally all with different values that get calculated totals checked. Same thing for Type 2. Then sort by Type.

I literally made a quick example using furniture of 2 types. the calculated Value is being divided by 12 in Result.

[Anonymous · Accepted solution]

You were correct, the method I suggested did not work. However after replicating your schedule I think I may have come up with a solution. Its not a pretty formula but I think it works. I still created a (result%) based off of the Result column only instead of it being <calculated total> I selected <Type>. Then I created a calculated Parameter "Rounded Result" with the Formula: (roundup(Result / result%)) / ((Result / result%) / Result)

see images below... as long as the Rounded Result column has calculated totals checked under Field Formatting: it works. Also I removed any field formatting for rounding decimals from the "Result" parameter. 

[rvtquestions]

Thank you @Anonymous

Seems to be working!

[asadkamalabbasi]

Thank you brother. It worked like a charm.

[Anonymous]

What value is <TYPE> referring to in your formula?

I have a similar case with a parking calculation chart and need to round up the results.

Thank you,