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:

Col Delane

Active Member
Joined
Jan 14, 2014
Messages
303
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?
 

salconflu

New Member
Joined
Mar 2, 2010
Messages
13
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.

DATEEGGS%SurvivalTU(day)TU(cum)E
9/6/2013024
9/7/2013025
9/8/2013025
9/9/2013025
9/10/2013025
9/11/2013175000.0525250
9/12/2013100000.0525500
9/13/201325000.0525760
9/14/201325000.05251010
9/15/2013175000.05251260
9/16/201325000.05241500
9/17/2013125000.05241740
9/18/2013125000.05241980
9/19/2013150000.05222200
9/20/201300.05222420

<colgroup><col><col span="4"><col></colgroup><tbody>
</tbody>
 

Col Delane

Active Member
Joined
Jan 14, 2014
Messages
303
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
 

Forum statistics

Threads
1,082,336
Messages
5,364,701
Members
400,811
Latest member
MSBINinja

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top