# Best Of

### Re: Checkbox formula with percentage

That should give you the number of all checkboxes. Now you need to divide that by the number of checkboxes that have been checked to get the percentage. It will look like:

=COUNTIF([Completed?]1:[Completed?]4, 1) / COUNT([Completed?]1:[Completed?]4)

### Re: Nested IFs to populate Priority based on set number of days prior to start date

You have directional quotes on the formula that doesn't work. Everything else looks correct about it

The typical causes of this are either developing the formula in word, or i've heard of foreign language keyboards or keyboard settings causing this. I recommend if you want to use a different application to build formulas either use a code editor, or use the stock version of notepad on your machine.

### Re: Formula to create a number assignment catalog

You can do this by creating a primary column which combines values from two other columns. I would likely create a **Product List** sheet that automatically assigns the proper number to a new product. The **Product List **would have a **Create New Product** form which asks for the category.

In the Product List sheet, create an `AUTOID`

column with this structure.

Create a column of type `Dropdown (Single Select)`

with each potential category. In this case `XX01`

, `XX02`

, and so forth.

For the primary column, I'd rename it to **Product ID** and put a column formula in it:

`[email protected] + "-" + [email protected]`

Populate the **Product List **sheet with all the existing products.

Create a form for users to add a new product.

Because you populated the product list already, adding a new product with the form will automatically assign a unique and new product number

### Re: Figuring out the Average within a Week

Hi Matt,

I'm glad to hear that worked!

Yes, you can just add another range and another criteria into the COLLECT function, you've got it... ex, something like this:

=AVG(COLLECT({Duration Column}, {Status Column}, "Z", {Week Number Column}, [Week Number]@row, {Revision Column}, >1))

### Re: Pulling data from a sheet

If I understand the requirements correctly, I think you'll want to look into the Copy Row or Move Row functions under the Automation tab. You can set those to whatever rules you want to specify and it will either copy or move the rows to your Error calculation sheet once the trigger has been set.

### Re: Accounting for the word "Perpetual" in a date field when there is no end date

=IF([Contract Expiration Date]@row = "Perpetual", "Perpetual", [Contract Expiration Date]@row - [Notify days to renew/cancel]@row)

That should do it

### Re: Modifying rows in a form entry sheet with formulas

**The new row will be auto-filled with the formulas if there are two rows below/above with the same formulas and the same level of hierarchy.**

More details:

If the formula structure is the same above/below the Formula Autofill will add the formula(s) automatically.

*Conditions That Trigger Formula Autofill*

*You’ll see **formulas** populate automatically when you type in a newly inserted or blank row that is:*

*Directly**between two others**that contain the same formula in adjacent cells.**At the**topmost of the sheet**if it’s**above two rows**that have the same formula in adjacent cells. (This includes rows inserted from a form.)**At the**bottommost of the sheet**if it’s**below two rows**that have the same formula in adjacent cells. (This includes rows inserted from a form.)**Above or below a single row**that is between**blank**rows and has formulas.*

More info:

**Also, if possible, I'd recommend using the new Column Formula because then it will always work.**

Did that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

**Andrée Starå** | Workflow Consultant / CEO @ **WORK BOLD**

✅*Did my post(s) help or answer your question or solve your problem? Please help the Community by **marking it as the accepted answer/helpful**. It will make it easier for others to find a solution or help to answer!*

### Re: Sum only the latest value

Here's one way to do it.

Add a helper column called MaxValue with this formula:

=IF([email protected] = MAX(COLLECT(Date:Date, ID:ID, [email protected])), [email protected])

Then your sum formula will be: =SUM(MaxValue:MaxValue)

I hope this helps.

Cheers,

Ramzi

### Re: Problem with error in from entry

The placeholder has to be triggered immediately, so the placeholder text is the same as when triggered. Still, yes, it could be optional as long as the user understands the difference and possible risk, maybe.

Yes, that's correct.

Let me know how it goes!

### Re: Gathering data from 1000 sheets

There is an update coming to reports, so you'll be able to sum and more in the report, so that might help.

Would that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

**Andrée Starå** | Workflow Consultant / CEO @ **WORK BOLD**

✅*Did my post(s) help or answer your question or solve your problem? Please help the Community by **marking it as the accepted answer/helpful**. It will make it easier for others to find a solution or help to answer!*