LinkBack URL
About LinkBacks
Hey all
Need some help in creating a unique running total formula.
I have created an example page for your viewing.
One week data entered two weeks a data not counted until all data from cell B16:G27 has been removed.
Thanks
Hey all
Need some help in creating a unique running total formula.
I have created an example page for your viewing.
One week data entered two weeks a data not counted until all data from cell B16:G27 has been removed.
Thanks
Last edited by Killer17; 09-25-2011 at 10:50 PM.
First question: Whats the difference between numbers in cells with yellow background vs numbers in cells with blue backgrounds?
Second question (which might be answered by the answer to the first question): Are the numbers in cells with yellow backgrounds dynamic (ie. determined by a formula) or static (ie. manually entered and unchanging)?
The yellow numbers not to be counted verse the blue cells amounts need to be counted.Originally Posted by Sofistikat
Example 1 New data entered in cell C15:E15
Count everything in blue cells, anything yellow cells don’t count C21:E21 & C37:E27
The remaining example show how it recounts week after week.
all Cells from C4:E15 numbers are dynamic determined by a formula.
I understand that only the blue cells are to be counted (summed). My question was intended to find out more about the nature of the yellow cells. In your first example, there are 2 rows of yellow cells, in the second, there are 4 rows, the third has 6 etc, so on and so forth.
What I need to know are the factors or formulas that determine how many rows of yellow cells there will be, which then determines the cells to be excluded from the sum.
I hope I'm being clear here.
Whenever I add new data I need two cells of the old data to not be counted. This should continue until all old data isn’t being counted anymore from cell C16:E27
1st time new data is entered I need the formula to stop counting two cells one from cell C27:E27 & C21:E21
The 2nd time new data is entered I need the formula to stop counting C26:E26 & C20:E20 and so on until all old data has been eliminated and its only counting current data.
Based on the example:
First: don't use merged cells... in the case of D3, remove the merge area, highlight D3:E3 and use Centre Across Selection option.
Second:
The above could be applied to each of your examples.
Please Login or Register to view this content.
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
Hey DonkeyOte
The formula works correctly until example 7 it calculates a total of 8914 when it should be 4016
The first 6 examples the formula works great.
Thanks
Last edited by DonkeyOte; 09-25-2011 at 01:51 AM. Reason: unnecessary quote removed
@Killer17, no need to quote prior posts in their entirety in your replies (only relevant parts)
Yes, it dawned on me whilst out yesterday afternoon that I had left a legacy reference in the formula (C43) which would generate the error you outline.
On further reflection, if we can assume:
1. that where a # (Col B etc) is "active" a balance will always exist in the first column
2. that the # (Col B etc) will always be 1:12
then we can simplify slightly
Please Login or Register to view this content.
=SUM(C4:F15)+IF(COUNT(C4:C15)<6,SUMPRODUCT(((MOD(B16:B27-1,6)+1)>(MOD(COUNT(C4:C15)-1,6)+1))*C16:E27))
Hey DonkeyOte thanks for the help that works perfect.
I do have other question if I wanted to extend the Col B cells from 1:12 to 1:34 how would I go about doing this?
Thanks again!
Last edited by Killer17; 09-25-2011 at 08:10 PM.
Killer , as DO already asked, DO NOT QUOTE ENITIRE POSTS UNNECESSARILY. Thank you !
Sorry my bad
I figured it out thanks again for your help
The formula works great on the test sheet but when I transfer it to my real page it’s not calculating correctly. I know the reason why
Here's the formula I have adjusted.
In Cell G27:I60 I have formula and its returning a result of 0 because no data has been entered.Please Login or Register to view this content.
With your formula is reading it as data and it’s doing the calculation incorrectly because of the zero’s in cells G27:I60
Thanks
If 0 is never a valid result then you might change the pre-emptive COUNT test to a COUNTIF test
The above assumes G27:G60 will only ever contain numerics.Please Login or Register to view this content.
If the above does not reflect reality I would suggest posting a representative sample file.
Ok Made the changes to the formulaIf the above does not reflect reality I would suggest posting a representative sample file.
and it's still not adding up correctly.Please Login or Register to view this content.
I have added an example sheet
Thanks
I believe the correct total should be 14367 (exclude 1 & 18 from bottom data set given existence of 1 in upper data set).
Irrespective of the above the existing formula still fails to calculate correctly and this is related to an earlier point where the implication was that the upper data set contained zeroes which should be excluded when determining set to calculate... hence my point below re: revision to COUNTIF:
In your latest sample the above is not reality ergo the COUNTIF generates an incorrect value... if you change <>0 to >0 then you will find you generate 14367. At this stage I think you need to be fairly clear in terms of requirements.
I would also add that given the multiple of Arrays in your lower dataset this model will perform very poorly in terms of calculation time.
Sorry DonkeyOte
I don't understand the.The above assumes G27:G60 will only ever contain numerics
Your right with the the changes to the formula it's adding correctly now.
I have one final question, what if I wanted to exclude 1 & 34 instead of 1 &18?
Exclude Examples
2 & 33
3 & 32
4 & 31
5 & 30
Thanks
Last edited by Killer17; 09-27-2011 at 11:56 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks