Count formula minus 1 - new to formulas

[Anonymous]

Hi,

I want to create an electrical schedule in my template that looks the same on each project, with a value of '0' or '-' if a particular item is not used in that project.

What I have done so far is placed one of each in the project and hidden them, then created my own 'Mycount' column hidden on the schedule.

I now want another column to show the actual calculated total of 'mycount' minus 1.  I can't seem to use the calculated total of the first formula in second formula that I put in as If(mycount=1,0,Mycount-1)   or  Mycount-1, it just turns it to zero.

ie. if the number of items calculated says there are 5 items, I would like it show 4 (to counteract the hidden item)

Can someone please tell me where I'm going wrong. I'm sure it's simple but I can't work it out.Have searched forum for days! Even tried several things including converting to percentage first but same issue.(see attached screenshot)

Thanks in advance for any assistance.

[barthbradley]

Formatting Tab.  

[Anonymous]

I'm not sure what you mean by that but on the formatting tab I have selected calculate totals

[lucdoucet_msdl]

@Anonymous 

Going out on a limb here but, is the reason you want to subtract 1 placed instance of a family from the total count in a project is that you’ve created a “legend” or some other equivalent illustration with an “extra” instance?

If so, it would be easier to place the “extra” instance on it’s own phase (that you could call “legend”) and filter the schedule to exclude that phase from the count.

If not, I would be curious to know why you need to report a result that is always one less that the amount modeled? Are they replacement units or attic stock? 😉

-luc 

[emodderman]

i agree with @lucdoucet_msdl . I would avoid placing unused elements into your project just for 'having them' purposes, as they cause way more trouble than it's worth. BUT if you have to, using a different phase would be a good way to get around your schedule counting them. 

Do you have a 1 in the value of Mycount in each item you place? I think the trouble with your formula might be the mycount-1... try this: if(Mycount = 1, 0, (Mycount - 1)) with the mycount-1 in its own set of brackets. 

[Anonymous]

Hi @lucdoucet_msdl  Thank you for your input. Yes, I wish to create a legend that is the same on each project, hence the need to have one of each item on the schedule but not necessarily in the project. I tried the phase thing but couldn't work out how to do that unfortunately.

[Anonymous]

Hi @emodderman Thank you for your input. That is perfect! What you have done is exactly what I've been trying to do for a while now but can't seem to get how you have come to this. I have tried exactly that formula before but it still doesn't seem to work.

My automatic 'count' column has the total number whether I select 'calculate totals' or 'no calculation' in the formatting tab, and 'MyCount' says exactly the same thing. My 'new count' always equals 0 as it uses just the value of 1. I know I'm missing something relatively simple but it's driving me nuts. This is what I get:

Be grateful for more of your input. Thank you in advance

 

[lucdoucet_msdl]

@Anonymous 

---

@Anonymous wrote:

Yes, I wish to create a legend that is the same on each project, hence the need to have one of each item on the schedule but not necessarily in the project.

---

As the root of the problem “one too many” seems to be the way you’ve set up your “legend” , I would suggest that implementing a solution that avoids the formula workaround will be less error prone in the long run.

If your “legends” are drawn in Revit legend views, these placed objects are not counted in quantity schedules as explained here: https://knowledge.autodesk.com/support/revit-products/learn-explore/caas/CloudHelp/cloudhelp/2020/EN...

Of course, the Legend View has it’s own limitations and you’ve possibly had to work around those problems with a regular view.

FWIW,

-luc 

[emodderman]

I think why you can't get the phased thing to work is because you want to see the item listed in the schedule whether it is there or not, and you can't get it listed there if the schedule is phased. (i guess this explanation isn't for you, but for others who suggested it) If those items are on a 'Legend' phase, you can't have them showing up on the 'New Construction' phase that will be scheduled...  unless i'm missing something like a nested phase or all phase... 

It definitely seems like you're making you're life more complicated... but, I believe it is possible to do, so lets do it!

so back to the formula...
Is MyCount also an integer? Is it a shared parameter? Do you accidentally have two MyCount parameters in the project?

I'm a bit puzzled. are you able to post a file? 

**EDIT** (again) OK, so you can't get the New Count to total a calculated total from the other column. thats the answer and the problem. 

Just like when you get a footer with a calculated total, that calculated total can't be used somewhere else in a formula (Autodesk, would you care to fix this please?) and so if the calculated formula is INSIDE the cell, the same thing applies, so it isn't using the Calculated Total as the MyCount total in the formula, it's using the individual Cell in the formula, so the formula ALWAYS see's MyCount as 1 and thus the formula is always 0. 

The only way (and they way i quick-tested it) is to physically change the MyCount to a number above 1. then the formula works... but that... isn't awesome. 

So i think we're back to square one, but now we know for sure why it isn't working. 

I think you will need to re-evaluate why you want this schedule to be the same whether the item is there or not... 

[ToanDN]

A formula wouldn't work here because it would be applied to every row, not the final result (aka a pivot table calculation).

In My Count column, enter 1 for actual elements and 0 for elements you want to exclude. The total of My Count is the quantity of actual project's elements and the total of Count is the quantity of project's elements + "place holder" elements.

[lucdoucet_msdl]

@emodderman + @Anonymous 

---

@emodderman wrote:

I think why you can't get the phased thing to work is because you want to see the item listed in the schedule whether it is there or not, and you can't get it listed there if the schedule is phased. (i guess this explanation isn't for you, but for others who suggested it) 

 I agree that the situation where a schedule needs to show a zero count for equipement families loaded in the project but not placed is a challenge. I would argue that this kind of schedule is contrary to good documentation practices in that it implies that this equipement is needed. For collaborators, it will elicit unproductive coordination questions and for contractors, it opens the door to estimation and construction errors.

I suspect it comes from a previous CAD and paper workflow where an empty table is set up to allow the design architect or engineer to markup the information for a drafting technician to complete. 

A single schedule view that tries to accomplish both these objectives will definitely be problematic as you are working against Revit's strengths. As an alternative, you could have a "design" schedule listing all loaded families allowing a "menu of choices" and a "tender" schedule that show's only modelled equipement once the initial design is set.

---

**EDIT** (again) OK, so you can't get the New Count to total a calculated total from the other column. thats the answer and the problem. 

Just like when you get a footer with a calculated total, that calculated total can't be used somewhere else in a formula (Autodesk, would you care to fix this please?)

---

I agree with @emodderman , your objective to remove only one item can't be done with a formula in the total.

If you insist on subtracting the "legend" items being included in the total count, you could include a yes/no parameter called COUNTABLE that is checked yes, if it is to be counted (ie modelled in the project) or unchecked, if it is part of the "legend". That way, your "MYCOUNT" would be setup with the following IF statement:

MYCOUNT = IF (COUNTABLE, 1, 0)

The total of MYCOUNT would then reflect only the equipment placed in the building project. 

As a quality control check (since the users could erroneously set COUNTABLE to the wrong setting), you could select all the equipment families in the project to COUNTABLE = 1 then go to your "legend" view and select all the equipment families in that view to COUNTABLE = 0.

-luc

ERRATA: This is a similar solution to what @ToanDN just posted but with a parameter in the family rather than manually adding a 1 or 0 in your schedule.

[Anonymous]

Ok @emodderman , so I've spent a few days trying a few different things and have taken on board what@lucdoucet_msdl and @ToanDN have said. I gave up the idea of making a formula and perssued the idea of using 'type images' on the schedule. This has worked brilliantly! I now have the images or symbols I use on the plans in the schedule without the need to have a set "hidden" to reflect them. I know that sounds somewhat convoluted, but it works for me. Thanks again everyone. I appreciate the time you all have taken to comment/reply.

[barthbradley]

....just wandering back by and wondering if my initially suggestion was a plus or a minus,  My livelyhood could depend on your answer.  I patiently await your verdict.  

Have a good day.  May the good Lord be with you.  

[lucdoucet_msdl]

@Anonymous 

For everyone's benefit, could you briefly describe your workaround?

Ideally, a partial screen capture of your schedule and the properties of the view. I am curious to see how this works with respect to your initial problem of having one too many items in your count.

Unless the initial objective of your schedule was not showing the correct count of placed equipment in the project but only to list the "available" equipment in your project?

Thanks,

-luc