-
Nesting SUM, Decimal places, and %
I'm attempting to calculate the percentage of work that's done, round it to a whole number, and add a percent sign. I can get 2 of any 3, but not all three. =SUM(([Points Done]@row / [Total Points]@row) * 100) Adding + "%" to the end of the equation gives me 5 decimals. I'd also like to add an IFFERROR for when there are…
-
Inventory Barcode Scanner / Expiration Date help
I made an inventory grid. One column has a barcode and one column has the expiration date. The expiration date reads the last 8 digits of the barcode to output a string of numbers that is the expiration date. Is it possible to make it formatted into a date like "dd/mm/yyyy". Right now it looks like this "ddmmyyyy"
-
Create formula to count category risk with aggregate rating
Hi, I have a risk register for a project that has well over 100 risks documented and I have them categorized into 17 different risks. I would like to be able to create a formula to count how many risks exist under each category and then a formula to show the aggregate risk associated with this. I would think by a…
-
COUNTIFS & #INVALID REF Error
Hi! I built a rollup sheet with the following formulas. They work, but when a row is deleted (probably added too), the formula on the reference sheet the rollup sheet causes the #INVALID REF error. I have to manually edit the formula to get it to work again. One section has about 50 rows and 3 columns of formulas.…
-
Can i reference a workspace in smartsheet formulas
I am currently referencing 3 ranges off a sheet as per attachment. I have a workspace which has many sheets with the same structure. Can I reference the workspace to pick up all the same data? If so, what would be the formula?
-
Inventory Management
I'm trying to find a way to remove inventory when an order is sold, other than manually changing the numbers. I have a form set up to add new orders to the bottom of the sheet. I need a formula to automatically remove the inventory items from each line in the Sold column. Example: Customer #1 bought 5 items of my Stock #2.…
-
Lookup/CountIF Formulas for Multi-Select ranges
Hello, We are referencing another sheet and are wanting to know how many times the word "SNAP" appears in the multi-select cells within a row. Right now, this is what we have, but it won't work. We have tried multiple different formulas as well and cannot seem to find anything to count the number of time "SNAP" shows up in…
-
Divide by Zero Message and IFERROR formula
I am getting a Divide by Zero Message when putting in this formula. It is correct because there are no items that fit this criteria to average. =AVG(COLLECT({Credit Granting Article Tracker Compliance}, {Credit Granting Article Tracker Month Completed}, "2", {Credit Granting Article Tracker Major?}, "No")) I attempted to…
-
SUM of Check Box IF Function that returns a value.
I've created several "IF" formulas on a sheet. An example of one is as follows: =IF([Kickplate Installation]1, "$42.86", ""). When the checkbox is checked, it does return my value of $42.86. However, I've also created a column to total all of the values returned. When I "Sum" the returned values from the formula, the "SUM"…
-
Changing % complete using a formula in another cell
I know you cannot use a formula in a "% Complete" field. Is there a way to update "% Complete" from a formula in another column (something like a "helper" column)? I have a "Task Status" column with drop down values of Not Started, In Progress, and Complete. I would like to change the value of "% Complete" when I change…