Consumption rate formula of rat bait to display on graph

[Mydc]

I am looking for someone to help me for a formula for the consumption rate over a period of time with periodic top ups.

An example is to show the consumption rate of rat bait by rats over periodic times for rat bait in various location around a home for example.

If I placed 2 bait blocks (each block being 50% each) in 7 different locations around a home (lets call then stations) and I checked these baits on 4 periodic inspection dates. The first periodic inspection is 15 days post the 14 blocks being laid with another 3 subsequent inspections every 25 days.

I have entered some data below that is an example of what might occur but this is as far as I can go as this is where i get stuck because the consumption rate may vary on each inspection per station or possibly remain constant per station per inspection but at the same time bait levels may be topped up for different stations at different inspection dates so i'm not sure how you calculation the constant consumption rate taking into account the rates of consumption between each inspection as well as the adding to the bait levels on varying occasions when bait levels are low or zero. And because of my dilemma I am also not able to even complete the other fictitious data for the other inspection days.



Recorded Amount of Bait Consumed on each inspection (the amount of bait remaining from the original !00%)
Station No. Day 0 (install day) 15 day inspection amount of bait added 40 day inspection amount of bait added 65 day inspection amount of bait added 90 day inspection amount of bait added
1 100% 20 0
2 100% 50 50
3 100% 60 0
4 100% 40 50
5 100% 0 0
6 100% 100 100
7 100% 100 100

If someone could help me with the formula to create a formula in excel so I can display a chart I would be very appreciative.

Another formula needed I to predict when future inspections are required on a per station bases before that level of bait per station is no less than say 20% which is based on the data history over the 90 days per each bait station eg a predictor tool. I believe this is a moving average formula. The formula needs to not only predict the next inspection date per bait station but also update its predictions each time an inspection is carried out on the prediction inspection date once the real date of those inspections is imputed eg the true level of bait remaining verses the predicted level eg "no less than 20%"

I hope this is easy for someone out there because for me its complicated

[MrShorty]

Are you assuming that the rate at which you add bait to each station is the same as the rate of consumption? With that assumption, I would probably use a "cumulative total added (not including initial starting amount)" type formula. Here's how I might set it up (recognizing that a big part of spreadsheet programming is arranging data/calculations in the sheet, so the details could easily change depending on how this step fits into the overall project):

0) Since you did not upload an actual spreadsheet, I copied your data table -> paste into a spreadsheet (starting in A2, so that I have a row above to add labels) -> text to columns to get the 4 columns of data into the spreadsheet.
1) Add a row with the "days since start" in it. B10 is 0, C10 is 15, D10 is 40 (and continue across as far as needed).
2) Copy station numbers into A11:A17
3) Of course, on day 0 (column B), the amount added excluding the initial amount is 0, so B11:B17 is 0.
4) The cumulative amount added can be a simple SUM() function -- paying careful attention to relative and absolute references In C11, I use the formula =SUM($C2:C2) (note the mix of relative and absolute references and make sure you understand how that is going to behave when copied). Copy and paste into C11:D17 (or as far across as needed).
5) For my chart, I would use the cells from steps 3 and 4 for my chart (Excel will probably default to "series in columns" when the chart is created, you will need to execute a "switch row/column" command and may need other edits to make sure Excel starts out with the correct chart).

From there, getting the rate of consumption (assumed to be the rate of addition), you would need to decide what assumptions you are going to make. For example, station 1 added 20 units of bait on day 15 (average consumption of 1 1/3 units per day =C11/C$10), but added nothing on day 40 (average consumption between day 15 and 40 of 0 or an overall average consumption of 0.5 units per day since day 0). Because part of the goal is to forecast future consumption, would you want to assume the highest consumption rate, an overall average, or assume that the rate for this station will be 0 and never check it again? It seems to me that there will be a lot of different assumptions one could make to make these forecasts, and I don't know that I am in a good position to make assumptions for you. You will need to decide what assumptions you want to make and develop a spreadsheet that can take those assumptions into account.

[Mydc]

Hi

Thank you for your reply

The answer to your question "Are you assuming that the rate at which you add bait to each station is the same as the rate of consumption?" is no I am not assuming that. The rate a which I add bait is not derived from the rate of consumption. The only correlation between these to variables is that 1 or 2 bait blocks (50% or 100%)can be added back into the station at any time when the bait level consumed is no less than 50%.

I hope I have answered your question and really appreciate you looking into my problem

[Mydc]

I have also added the excel doc attachment

[MrShorty]

I suppose that answers my opening question, but I am at a loss for how to proceed. If you are not going to assume that the rate of addition is the same as the rate of consumption, then I do not see anything in your data that tells me how much bait the rats are consuming.

Maybe some of that is that I don't understand what the numbers in your attached sheet mean -- specifically the values in C and D. What do these columns represent?

The only suggestion I might make is that I don't think I would do this on a percentage basis -- at least not to start with. I would want to do the calculation on an absolute basis (record mass or pellets or other absolute unit that measures the amount of bait in each station) and perform the calculations that way. If I later want to express it in relative percentage terms, do that calculation later after I have a better understanding of the consumption in absolute terms.

If I am not assuming rate of addition=rate of consumption, then I would expect to build a table where the left column is the station number, the top row is # of days since start, and each entry in the table indicates the amount of bait (in my chosen units) that has been consumed from each station on each day. Somewhere along the way, I expect I will need to know how much total bait has been added to each station, how much is present in each station before I add, and anything else I discover that I need. Once I have some measure of how much bait has been consumed at each station, then I can make a chart and otherwise analyze the data to get some idea of how fast the bait is being consumed.

[Mydc]

Hi MyShortly

Thank you for your reply.

I am really for the lack of detail on your latest question but now I realize i did not provide you with all the info required in order to get the right answer.

In regards to your response "I suppose that answers my opening question, but I am at a loss for how to proceed. If you are not going to assume that the rate of addition is the same as the rate of consumption, then I do not see anything in your data that tells me how much bait the rats are consuming." & "Maybe some of that is that I don't understand what the numbers in your attached sheet mean -- specifically the values in C and D. What do these columns represent?"
The numbers in column C are the amount of bait remaining in the stations on day 15 inspection post the stations being laid around the house (remembering the at the time they were laid the stations were 100% full) & column D is the amount of bait replenished in the stations on day 15.

Does this now clarify things for you?

[MrShorty]

I think we are getting closer to understanding. I would still make my units consistent. Column B is "per cent full", but I don't know what the numbers in C and D are. As I noted in the previous response, I would perform the calculation in absolute terms (grams or whatever of bait). For my benefit, I replaced the 100% in column B with the number 100 -- meaning 100 of the same units as C and D.

So you are keeping track of how much bait is present at the start of the inspection, and then noting how much is added. The "amount consumed between inspections" should then be "amount in trap at the end of the previous inspection" - "amount in trap at start of this inspection" (column B - column C). The "amount in trap at the end of this inspection" should be "amount in trap at start of this inspection" + "amount added to trap during this inspection" (column C + column D). The "average rate of consumption between inspections" is then "amount consumed between inspections" / "number of days between inspections" (column B-column C)/15 [I'm assuming you will express the rate of consumption as units/day].

Are you required to arrange the data like you show in your sample spreadsheet (one row for each station and multiple columns for each inspection's activities)? I find that a big part of spreadsheet programming is arranging the spreadsheet. For a "time series" analysis like this, I would tend to want to have 1 row for each day's activities, and multiple columns for each station. Here's how I would set this spreadsheet up:

1) in column A (A2:A6), enter my inspection days 0, 15, 40, 65, 90
2) in column B, enter my "in trap at start of inspection" values for station 1. Note that, for day 0 (B2), this can be 0 or N/A or whatever.
3) in column C, enter my "added during this inspection" values for station 1. For day 0 (C2), this will be the starting value (100 or whatever it happens to be).
4) in column D, calculate my "in trap at end of inspection" values for station 1. This is a simple D2=SUM(B2:C2).
5) in column E, calculate my "consumed between inspection days" values for station 1. For day 0 (E2), this is simply 0. For days 15+, this is a simple subtraction E3=D2-B3
6) I would expect to want a "running total of bait consumed" column, which should be a simple SUM() function F2=SUM(E$2:E2). Note the mix of relative and absolute references.
7) To get a chart, I would select columns A:F and insert a chart (I prefer scatter charts), and I should get a good chart that shows me those 5 quantities against time/days.
8) repeat columns B to F going to the right in the spreadsheet for the other stations.

Now we need to understand exactly how you intend to analyze the rate of consumption. I probably would not make any decisions until after I had looked at the chart to see what it shows me.

As noted, that assumes you are allowed to rearrange the spreadsheet.

That's not the final analysis, but that should give you something to at least begin to visualize bait consumption. From there, help us understand how you want to analyze the consumption rate and forecast inspection dates and we'll help you program that into the spreadsheet. What part do you get stuck on?

[Mydc]

Hi Again

Thank you for your reply once again and it is now becoming clearer to me and I now understand that I cannot combine all the stations in one table and that the table of data needs to be per bait station.

I have taken your advice and am using a weighted measurement instead of percentage with 1 bait weighing 28 grams therefore when stations are first laid there is 56 grams of bait in the station (100%).
When a user is assessing the bait level in column B they will be still assessing it as a percentage but the spreadsheeting will display it as number of grams. In our application users have the option of choosing a percentage from a dropdown list from 0 - 100% in increments of 10 eg 10%, 20% etc therfore each 10% equals 5.6 grams

1) in column A (A2:A6), enter my inspection days 0, 15, 40, 65, 90
2) in column B, enter my "in trap at start of inspection" values for station 1. Note that, for day 0 (B2), this can be 0 or N/A or whatever. Day 0 is actually 100% (56grams) as the station are 100% full of bait when they are laid on day 0
3) in column C, enter my "added during this inspection" values for station 1. For day 0 (C2), this will be the starting value (100 or whatever it happens to be). C2 for day 0 is actually 0 because the stations are already 100% full as not in the point above
4) in column D, calculate my "in trap at end of inspection" values for station 1. This is a simple D2=SUM(B2:C2).
5) in column E, calculate my "consumed between inspection days" values for station 1. For day 0 (E2), this is simply 0. For days 15+, this is a simple subtraction E3=D2-B3 the sum for this is actually E3=SUM($D2-B3)

Ideally I need to graphs
1) displays the rate of consumption over the 90 days (based on column F i think ?)
2) displays a prediction when the next inspection is due post the 90 day inspection with the prediction date to be no later than before a station only has no less than 11.2grams of bait remaining. (needs to take into account the fact that the consumption rate can vary per inspection day.This graph will be based on column E I am guessing ?.
Long term I would like the next predicted inspection date to be refined post each consecutive predicted inspection date (obviously the more inspection data (more inspection days) the more accurate the next predicted date of inspection will be). The measurement for the next predicted inspection dates will be in a date format as each inspection day will be time stamped eg day 0 may be the 1/8/19 and inspection day 2 (day 15) the 15/8/19 etc. Need to also bear in mind that the inspection dates for inspections 2, 3, 4 & 5 may vary and may not necessarily done exactly on day 15, 40 etc.

I have attached the revised spreadsheet for your reference

[Mydc]

Sorry but the is the right file. As you can see I have attempted to do a graph but not right

If you could explain this it would be much appreciate

[MrShorty]

The graph looks correct to me. I'm not sure why you chose to put the "bait consumed" on the horizontal x axis and "inspection day" on the vertical y axis, but it is correct for the choice that you made.

If you did not intend to make that choice, Excel made that choice for you because your "bait consumed" values are in the "left" column and your "inspection day" values are in the right column, and Excel defaults to using the left column for x axis and right column for y axis. A simple switch of the columns would put "inspection day" on the horizontal axis and "bait consumed" on the vertical axis.

Either way, the chart is showing, as one would expect, a decreasing rate of consumption. I'm not sure how you intend to quantify the rate of consumption, though, so I am not sure what to recommend as far as programming those calculations into the spreadsheet.

If you have no idea how you want to analyze the data, then it is probably time to step back from the spreadsheet programming and think about the data analysis parts of this. I see several possibilities, but cannot be sure exactly how you would want to proceed. The easiest might be to simply compute "average consumption rate" for each inspection period (for the last period for station 1, it would be (124.8-112.0)/(90-65)), assume that will be the same rate as the next period, and use that to estimate the next inspection date.

Maybe compute average consumption rate for each period (In D15, enter =(B15-B14)/(C15-C14) and copy down), then add a chart to show average consumption rate against inspection day, and see if that helps decide how to analyze this.

More complex would be to regress a suitable modeling equationto the data (the empiricists favorite tools are polynomials), then you can use some simple calculus to estimate the consumption rate by taking the first derivative of that curve, then estimate the rate of consumption going forward to determine the next inspection date.

If I let my mathematician and computational biologists loose, I can envision some very complex ways of doing this.

Basically, you are at the point in the project where you need to decide how you want to proceed based on the desired goals of the project and the desired complexity and any other considerations that you want to bring in. These are not necessarily decisions I can make for you.

[Mydc]

Hi again

I dont think the average consumption rate will give a prediction of any accuracy so ruling that one out as.

I think your second idea "More complex would be to regress a suitable modeling equationto the data (the empiricists favorite tools are polynomials), then you can use some simple calculus to estimate the consumption rate by taking the first derivative of that curve, then estimate the rate of consumption going forward to determine the next inspection date." will be more benificial particularly with more long term data.

If you are able to assist with that it would be a great help but will leave that up to you. I have also updated my graph as per the attached excel doc.

Let me know what you think

Kind Regards.

Darren

[MrShorty]

Oftentimes, the hardest part of a "regress to a suitable model and process from there" approach is choosing a suitable model (note that Excel cannot choose a model for us). We as programmers have to know enough about our problem to choose a suitable equation, then program the regression into Excel.

Once a suitable equation is chosen, then regressions are usually pretty easy in Excel. If your chosen equation is "linear" (such as a polynomial or an exponential growth/decay or a power function...), then you can use the built in LINEST() function. If your chosen equation is "non-linear", then you can build the spreadsheet to use the built in Solver to perform the regression.

I notice that, in this spreadsheet, you have replaced "elapsed days" from the previous sheets with calendar days. Some regressions won't care whether you use elapsed days or calendar days, but some might. It might be useful to add an elapsed days column (=A2-$A$2) for those cases where the computation decides to work better for elapsed days.

As a quick example, I guesstimated that a simple square root function might be a good approximation. Something like totalconsumed=m*SQRT(elapseddays)+b (because this regression was one that worked better for elapsed days). Assuming you add an elapsed days column, then you can use the LINEST() function like =LINEST(F2:F6,SQRT(elapsed days)) (select two adjacent horizontal cells -> type in function -> confirm with ctrl-shft-enter because LINEST() is an array function). That looks like it gives a reasonable fit of the existing data for station 1 and should allow for a reasonable estimate of the rate of consumption for the next inspection period. That should illustrate the basic procedure using a simple linear function and Excel's LINEST() function.

You may need to use OFFSET() or other dynamic functions to define the known_x and known_y ranges for LINEST(), so that the regression will automatically adapt as new data is added to the tables.

Will it hold up as more data is added? I don't know. This is where the choice of regression equation can be important. The better your choice of regression equation, the more robust your predictions will be.