Incrementally returning a value based on relative running totals

salconflu

New Member
Joined
Mar 2, 2010
Messages
13
I have what has turned out to be a challenging problem. I am trying to model emergence timing of eggs based on cumulative temperature units. When the eggs (Column L) deposited on a given date (Column C) have accumulated 1600 tu's (a running total of Column N) I wish to return the original number (Column L) to a column (Column Q) in Excel. I have also tried returning the date at which cumulative tu's reach 1600 with dget. Unfortunately, I can't figure out how to use Dget or some other function to deal with the relative sums for each value and step through to the next value when the first is returned. I can get the first number by entering the following formula into Column Q, =IF(SUM(N$9:N18)>1600,M$9,0) but I don't know how to increment it once the first value is returned. Any suggestions for approaching this problem would be greatly appreciated.
 
Last edited:

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Howdy
On the face of it this doesn't look too difficult, but I'm having trouble picturing your sheet layout, and how the incrementing is meant to work.
Would you be able to either upload the table, or post a simple representation of it, showing all relevant columns and several rows to show the source data & calc's for the additional bit that you're trying to get to work?
Please also advise:
1. what version of Excel you're using?
2. if necessary, can a macro/VBA be utilised?
 
Upvote 0
Thanks for the reply. I am using Excel 2010. I am always interested in learning, so a VBA/Macro approach would be welcomed. Below is a snippet of my table. Eggs begin incubating on the date shown. Once the TU's/day for each Date reach 1600 they hatch. As you can see from the table, the running total I have is only good for the first date. So I have resorted to a massive date x date array. But I would sure like to find a cleaner. more sophisticated approach. Thanks.

[TABLE="width: 370"]
<colgroup><col><col span="4"><col></colgroup><tbody>[TR]
[TD]DATE[/TD]
[TD]EGGS[/TD]
[TD]%Survival[/TD]
[TD]TU(day)[/TD]
[TD]TU(cum)[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD="align: right"]9/6/2013[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="align: right"]24[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9/7/2013[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="align: right"]25[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9/8/2013[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="align: right"]25[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9/9/2013[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="align: right"]25[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9/10/2013[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="align: right"]25[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9/11/2013[/TD]
[TD="align: right"]17500[/TD]
[TD="align: right"]0.05[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]9/12/2013[/TD]
[TD="align: right"]10000[/TD]
[TD="align: right"]0.05[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]9/13/2013[/TD]
[TD="align: right"]2500[/TD]
[TD="align: right"]0.05[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]76[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]9/14/2013[/TD]
[TD="align: right"]2500[/TD]
[TD="align: right"]0.05[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]101[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]9/15/2013[/TD]
[TD="align: right"]17500[/TD]
[TD="align: right"]0.05[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]126[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]9/16/2013[/TD]
[TD="align: right"]2500[/TD]
[TD="align: right"]0.05[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]150[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]9/17/2013[/TD]
[TD="align: right"]12500[/TD]
[TD="align: right"]0.05[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]174[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]9/18/2013[/TD]
[TD="align: right"]12500[/TD]
[TD="align: right"]0.05[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]198[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]9/19/2013[/TD]
[TD="align: right"]15000[/TD]
[TD="align: right"]0.05[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]220[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]9/20/2013[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0.05[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]242[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Hi again

Thanks for the additional info, however, I'm still a little confused.

I can see that your current cumulative TU(cum) is a cumulative total of ALL dates. However, as the table shows only one entry per date, the TU(day) for each date is also its cumulative, so why do you need a formula to sum one entry?? (I may well have misunderstood something here!) How do you get more than one entry per date, and/or increment the TU(day)?

Can you also please re-post the sample data with the relevant column references (A, B, C...) above the headings, and indicate which row the headings are in.

I think the solution will be quite simple once I better understand your data.
Cheers
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top