Average formula

Deanna Vandermeer

I am sure this is rather obvious, however I am not seeing where my mistake is. All I am trying to do is return the average cost of a product cost column but I need to not include any $0.00 dollar amounts that are populating (which will happen when there is no inventory at the moment). Can someone please advise where my mistake is?

=AVG([Protocol Cost w/ Freight]:[Protocol Cost w/ Freight], <>0)

Thank you

Paul Newcome

Try this...

=AVERAGEIF([Protocol Cost w/ Freight]:[Protocol Cost w/ Freight], <> 0)

Paul Newcome

I didn't realized that you had your ordered vs used set up the way you do. I built my formula based on it being similar to a dropdown selection in a separate column.

Instead it looks like you have two separate columns and determine ordered vs used by which column the number is in. If that is correct, try this...

=INDEX({Inventory Beginning Count}, MATCH(Assessments@row, {Inventory Assessment Full Range}, 0)) - SUMIFS([Assessments Used]:[Assessments Used], Assessments:Assessments, Assessments@row) + SUMIFS([Order Received]:[Order Received], Assessments:Assessments, Assessments@row)

Are you wanting to also incorporate the dates?

Paul Newcome

Ah. Understood. Ok. So we can still use an IFERROR.

=IFERROR(original_formula, replace_error_with_this)

Basically the IFERROR will allow the original_formula to run until the original_formula produces an error. Then it will output whatever you have in the replace_error_with_this section. So you want to run the original_formula until it throws an error. Then you want to replace it with [Beginning Inventory]@row.

=IFERROR(original_formula, [Beginning Inventory]@row)

=IFERROR(INDEX({Assessment Requests Current Inventory}, MATCH([Assessment Name (List)]@row, {Assessment Requests Assessment Name}, 0)), [Beginning Inventory]@row)

Paul Newcome

That's actually exactly what I was referring to when I mentioned live vs running totals and incorporating the dates.

Basically we would modify the formula to only account for dates that are on or before the date in the current row.

=INDEX({Inventory Beginning Count}, MATCH(Assessments@row, {Inventory Assessment Full Range}, 0)) - SUMIFS([Assessments Used]:[Assessments Used], Assessments:Assessments, Assessments@row, Date:Date, @cell <= Date@row) + SUMIFS([Order Received]:[Order Received], Assessments:Assessments, Assessments@row, Date:Date, @cell <= Date@row)

Paul Newcome

Try this...

=AVERAGEIF([Protocol Cost w/ Freight]:[Protocol Cost w/ Freight], <> 0)

Deanna Vandermeer

Thank you Paul, you are the best formula assistance - always to the rescue. I appreciate your help, always.

Now - with that said, ready for another formula ... :)

I have two sheets so this one does need to be a cross-sheet and same-sheet formula so I have tried several different options including INDEX/MATCH and SUM(COLLECT and so far I am not getting what I need. Basically, on the "request" sheet I need to keep a running total in a column that is looking at the "inventory" sheet, finding the "beginning inventory" information and matching it to the specific "Assessment" by name and then after returning the beginning inventory number from the inventory sheet I need to subtract from a number in a column if the assessment is "used" AND also add a number in a column if the assessment is "ordered". In other words - Beginning Inventory - Assessment Used + Assessment Ordered = Running Total. Here is the current formula which is at least pulling in the correct "Beginning Inventory" for that assessment when it finds a MATCH on the row to the Assessment Name. Any ideas on how to make this work and return an accurate running total?

=INDEX({Inventory Beginning Count}, MATCH(Assessments@row, {Inventory Assessment Full Range}, 0))

Paul Newcome

Are the Used and Ordered values on the same sheet as the formula or on the same sheet as the original beginning count? Either way you are going to end up using a SUMIFS along the lines of...

=SUMIFS({Column to Sum}, {Used/Ordered Column}, "Used", {Assessments Column}, Assessments@row)

Then we would do another one like it except swap out "Used" for "Ordered"

=SUMIFS({Column to Sum}, {Used/Ordered Column}, "Ordered", {Assessments Column}, Assessments@row)

Then we would combine them all...

=INDEX({Inventory Beginning Count}, MATCH(Assessments@row, {Inventory Assessment Full Range}, 0)) - SUMIFS({Column to Sum}, {Used/Ordered Column}, "Used", {Assessments Column}, Assessments@row) + SUMIFS({Column to Sum}, {Used/Ordered Column}, "Ordered", {Assessments Column}, Assessments@row)

Deanna Vandermeer

So this still isn't quite what I am needing, but I think we are getting there. The Inventory sheet is where I am using a cross-sheet INDEX/MATCH formula to pull in the beginning inventory count for a specific assessment name. This is being pulled in with my INDEX/MATCH formula to the Assessment Requests sheet and is in a column titled Current Inventory which is where I am hoping to be able to create a running total based off of new requests (used assessments) or (ordered additional assessments). I am going to add a screenshot and perhaps I need to add an additional column to actually make this work to return a running total based off of the assessments column data (there are upwards of 235 different assessment names). In other words, I need to pull over only one thing from the Inventory sheet where all the data is kept for all assessments which is the information contained in the Initial/Beginning Inventory column and match it to the Assessment name in my target sheet for Assessment Requests. I need to be able to calculate as assessments are either used (subtract) or ordered (add) from the Current Inventory calculation for each specific assessment name. Anyway, I hope this makes a little more sense and with my screenshots perhaps it will help provide a better visual. You are just so amazing with your formulas and I do value any advise or input you have to offer. Thank you.

Paul Newcome

I didn't realized that you had your ordered vs used set up the way you do. I built my formula based on it being similar to a dropdown selection in a separate column.

Instead it looks like you have two separate columns and determine ordered vs used by which column the number is in. If that is correct, try this...

=INDEX({Inventory Beginning Count}, MATCH(Assessments@row, {Inventory Assessment Full Range}, 0)) - SUMIFS([Assessments Used]:[Assessments Used], Assessments:Assessments, Assessments@row) + SUMIFS([Order Received]:[Order Received], Assessments:Assessments, Assessments@row)

Are you wanting to also incorporate the dates?

Deanna Vandermeer

Yay! That worked perfectly. Thank you so very much. We don't need to incorporate the dates, but if we did - for example - for each school year (or preferably our Fiscal Year which is always July 1 - June 30), what would be the best way to do that if they decide they want that too?

Paul Newcome

It would depend on how exactly you wanted to incorporate them.

What I was thinking is that right now... Every row will update as numbers change. Every row would show a LIVE total as opposed to a RUNNING total.

Deanna Vandermeer

Hi again, since we are on a roll, do you know a good way to add to this cross-sheet formula so that it will return the Initial/Beginning Inventory IF it does not showing on the Assessment Request sheet as being used OR ordered? I am including screenshots of my Inventory sheet where I have another column to return Current Protocol Inventory but when it isn't on the Assessment Request sheet it is returning #NO MATCH and I am wondering if I could have it return what is in that same sheet Initial/Beginning Inventory if nothing is currently in the Assessment Request sheet. I hope the screenshot helps AND again, thank you for being the superhero of formulas!

Paul Newcome

I am not sure I completely understand what you are asking for, but let's give this a shot...

=IFERROR(INDEX({Inventory Beginning Count}, MATCH(Assessments@row, {Inventory Assessment Full Range}, 0)), 0) - SUMIFS([Assessments Used]:[Assessments Used], Assessments:Assessments, Assessments@row) + SUMIFS([Order Received]:[Order Received], Assessments:Assessments, Assessments@row)

Deanna Vandermeer

On this one (and so sorry for not providing a better explanation), I am on the Inventory sheet so I actually don't need to run any SUM formulas but rather I am using a cross-sheet formula to pull over the data from the Assessment Request Current Inventory sheet and INDEX/MATCH to the Assessment Name on the Inventory sheet itself. If there has not yet been any requests made on the Assessment Request sheet, I simply want the Inventory sheet to return the number in the column on the Inventory sheet that reflects the Initial/Beginning Inventory. So my formula on the Inventory sheet is...

=INDEX({Assessment Requests Current Inventory}, MATCH([Assessment Name (List)]@row, {Assessment Requests Assessment Name}, 0))

Sorry for being confusing. The cross-sheet formula is going out to the Assessment Request sheet and pulling in a match of the running total (live) formula that you helped me with for the Assessment Request sheet. But in this case I am needing a new formula on the Inventory sheet itself which looks at that column data on the Assessment Request sheet and returns that number when it matches the assessment name in the Inventory sheet, otherwise if it finds no match I need it to return the data that is in the column on the Inventory sheet that is the beginning inventory. Right now my formula works except when it finds no match and that is because some assessments haven't been used this year.

Paul Newcome

Ah. Understood. Ok. So we can still use an IFERROR.

=IFERROR(original_formula, replace_error_with_this)

Basically the IFERROR will allow the original_formula to run until the original_formula produces an error. Then it will output whatever you have in the replace_error_with_this section. So you want to run the original_formula until it throws an error. Then you want to replace it with [Beginning Inventory]@row.

=IFERROR(original_formula, [Beginning Inventory]@row)

=IFERROR(INDEX({Assessment Requests Current Inventory}, MATCH([Assessment Name (List)]@row, {Assessment Requests Assessment Name}, 0)), [Beginning Inventory]@row)

Deanna Vandermeer

WOW! You are simply the best. I value all your help with this today. You truly are a Smartsheet formula SUPERHERO!

Thank you, thank you, thank you - this worked like a charm!!

Paul Newcome

Happy to help! 👍️

Deanna Vandermeer

Paul -

Do you know if there is a way to also have an additional column with a formula that would keep the running total of assessments used in each row when a new assessment request comes in? The "live" total formula is amazing but now they are asking if there is a way to also have a formula that will show the math that shows the inventory at the time of the request (either a used assessment or an ordered assessment). Basically, in their old way of doing things, in an Excel workbook with 200+ sheets (one for each assessment), they were tracking the inventory as it was subtracted from or added to with each row/new entry. What I am trying to avoid is having to maintain 200+ individual sheets for each assessment but instead use reports to surface each assessment, especially since we can't afford Control Center and managing that many sheets would be not ideal.

So the scenario would be the main assessment request intake sheet (via a form) is where I am currently tracking the inventory use and or orders for each individual assessment. But they want to not only see the live inventory total by each assessment but the math showing how the inventory is being impacted by each use or order. The old formula in their sheets was simple but it was because they used a single sheet for each assessment. So it with each new row, the formula was subtracting or adding to the current inventory column from the row directly above it. But because I have all assessments coming in to one single sheet, I need the Match to make sure the formula is only subtracting from the last entry that was for that particular assessment.

Do you have any ideas on ways to help accomplish this for them without having to manage one sheet per each 200+ assessments? Any advice or ideas will be greatly appreciated. Thanks.

Deanna

Paul Newcome

That's actually exactly what I was referring to when I mentioned live vs running totals and incorporating the dates.

Basically we would modify the formula to only account for dates that are on or before the date in the current row.

=INDEX({Inventory Beginning Count}, MATCH(Assessments@row, {Inventory Assessment Full Range}, 0)) - SUMIFS([Assessments Used]:[Assessments Used], Assessments:Assessments, Assessments@row, Date:Date, @cell <= Date@row) + SUMIFS([Order Received]:[Order Received], Assessments:Assessments, Assessments@row, Date:Date, @cell <= Date@row)