I was wondering what I would need to do to make this formula return the accurate number?

Deanna Vandermeer · 07/31/20

At the moment, I have a formula that is not actually doing what I need it to do. Basically what I have is an inventory sheet and when folks remove from the inventory I need it to look at the cost of that item from the cost column in that specific row and return a total $ Amt Used. However, occasionally, the item is returned and needs to be returned to inventory (which should just zero out the amount on that row. In addition, when we add to the inventory we need to indicate zero for the amount used in that row. When an assessment is purchased, we use a column [Ordered/ Received] and will insert a new row when placing the item into inventory for that particular assessment, but when it is being returned we just add the number back into the row. So I am a bit stumped on this one at the moment can anyone help me? Here is the current formula which is not returning a correct number and I have already tried place a minus sign in front of the bracket before [Ordered/ Received] and I have also changed the number in that row to a negative number, nothing is working. Here is the current formula

=([Number of Assessments Requested]4 * Cost4) - ([Ordered/ Received]4)

Here is a screenshot of part of the sheet.

Brian Campbell · 07/31/20

Hello, what is happening is you are getting the Cost from the Number of assessments * Cost = Total Dollar Amount, then you are just subtracting the number ordered/received which doesnt account for the cost associated with that. I believe you have to put the * Cost4 at the end of the formula "=([Number of Assessments Requested]4 - [Ordered/ Received]4)* Cost4)"

Deanna Vandermeer · 07/31/20

I think I solved my formula but now am not sure how to properly account for when we are adding to the inventory. Keep in mind I do have cross-sheet formulas returning a current inventory count as well as a running total count. Those look at our inventory sheet. So now that I am getting a zero to return on the row where the inventory was just returned (not used), I am not getting an accurate number on the $ Amt Used column but maybe I have to change this to be two columns? A returned column & an ordered column to make it work??

This was my first attempt to add to the inventory, as you can see the red 0 is being returned in the Current Inventory (running total) column. It was showing -14 originally. But by putting in a negative 14 when "adding" to the inventory it returned a negative Amt Used number. So then I changed it to a positive number and now I am getting a large amount in the Amt Used but we are really wanting it to not include when we add to the inventory in the amount used column so I am thinking I need to add an additional column to capture what we are needing. I'll keep working on this, thanks.

Brian Campbell · 07/31/20

Hello, what is happening is you are getting the Cost from the Number of assessments * Cost = Total Dollar Amount, then you are just subtracting the number ordered/received which doesnt account for the cost associated with that. I believe you have to put the * Cost4 at the end of the formula "=([Number of Assessments Requested]4 - [Ordered/ Received]4)* Cost4)"

Deanna Vandermeer · 07/31/20

Yes, that was correct. I actually was able to solve that myself but then ran into another issue. Thinking we may need to completely change up our Protocol Requests sheet to get accurate number returns, thanks.

I was wondering what I would need to do to make this formula return the accurate number?

Best Answer

Answers

Help Article Resources

Categories