LinkBack URL
About LinkBacks
Hi there,
The problem I hope you will help me out with, is hard to explain in words. But I did my best to make it as clear as possible in the attached worksheet. So, could you please have a look at it . Thank you very much.
Hi there,
The problem I hope you will help me out with, is hard to explain in words. But I did my best to make it as clear as possible in the attached worksheet. So, could you please have a look at it . Thank you very much.
Last edited by Michael6; 10-13-2009 at 09:24 AM.
What exactly does this mean>
So for February selected.. what calcs should happen?Calculations to start from the chosen month [in cell 'F7' till the end of December, Cell 'D60'.This is impotant mainly for the progressive sum to be calculated from a particular month
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
What exactly does this mean>
So for February selected.. what calcs should happen?Calculations to start from the chosen month [in cell 'F7' till the end of December, Cell 'D60'.This is impotant mainly for the progressive sum to be calculated from a particular month
NBVC, Thank you for your reply. Could you please have a look at the attached modified worksheet sample. What is now needed is one single formula to give us the sums in column E. No need now for selecting a particular month [i.e ignore cell F7]
Last edited by Michael6; 10-08-2009 at 05:04 AM.
If you want one formula to "unite them all..."
can no doubt be streamlined but I'm about to head out door...
Please Login or Register to view this content.
(above does make assumption that months are listed sequentially (and without gaps))
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
DonkeyOte, thank you very much. The formula is great; it is the one I've been looking for. But it needs some modifications to give accurate results.
**The problem is: If a value is entered in column 'C' in the top row of any month, it will wrongly be added to to the " Month Credit" of the preceding month.
** Another thing is that the last "Progressive Total" calculated is copied down to the end of the table for the rest of the months that have no values entered yet.
**One more thing (not so important), is the appearance of the zero values. It will be better if they don't show.
The sample worksheet , with the problem pointed out on it is attached
Regards
Michael
Revised:
To hide the 0's simplest thing to minimise calcs would be to use a Custom Format to do this (assumes 0 never a valid output), eg: [=0]"";GeneralPlease Login or Register to view this content.
(adjust as necesary)
DonkeyOte, the formula is now perfect and works quite fine.
I can't find the words to thank you. With you on this forum, one can be assured of getting the help he seeks.
Back to you again DonkeyOte.
I've just encoutered a problem in applying the formula, which is explained in the attached sample worksheet.
Could you please have a look and advise me of a solution. Thanks
This occurs because the value being added (eg K61) throws the sequence of numerics out of sequence which in turn causes the MATCH to return the incorrect row - in turn enlarging the range to be summed - leading to incorrect results.
Remember dates are integers ... on 1900 Date System:
30 Nov 2009 --> 40147
conversely
111 --> 20 Apr 1900
So by adding the 111 below the dates you end up with an unsorted date sequence of:
1 Oct 2009
1 Nov 2009
1 Dec 2009
20 Apr 1900
Conducting MATCH with return_type of 1 (default where omitted) will lead to inconsistent results, eg:
=MATCH("30-Nov-2009",date range)
will return position of 20 Apr 1900 and not that of 1 Nov 2009 which is the requirement.
To correct we will need to better understand what the significance of this added value is - ie why are you adding it ?
Is your intention such that "Progressive Total" will always appear or is this for sake of demo. ? If it always exists you can revise the approach to use that value, eg;
Please Login or Register to view this content.
Sorry I was not quite clear in explaining the problem.
It is only that the caculated values of 'Month total' & 'Progressive Total' for Dec/09 are missing, [without me adding the number 111 to the table in question].
Can you just confirm the values in F will always appear - ie they are not listed purely for sake of demo.
Given I can't guarantee I'll be around when you next post - assuming the answer to the above is yes then you could revise your approach such that:
Custom format applied to E10 onwards of say: #;;Please Login or Register to view this content.
Column F contains the items which are always there. Column E should contain their caculated values. Only the values in columns C & D are for the sake of demo.
I will try the formula yo just gave me and see how it works
I've just tested this new formula and it is working OK.
Thanks for you patience with me.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks