LinkBack URL
About LinkBacks
Hola,
Need your help in finding the appropriate formula for this task.
Explanations inside the file.
Thanks, Al
Hola,
Need your help in finding the appropriate formula for this task.
Explanations inside the file.
Thanks, Al
Last edited by alfredkri; 01-25-2011 at 02:37 PM.
You should post the question as well s the file. Not everyone is able to download
Hope that helps.
RoyUK
--------
For Excel Tips & Solutions, free examples and tutorials why not check out my web site
Free DataBaseForm example
It's not clear what should happen where 2 or more dates share the max. - returning all dates in a single string would be difficult with formulae.
Assuming you're not looking to use helpers (though you should) you could retrieve the first date using:
The count of events can be retrieved with:
Please Login or Register to view this content.
Note: Spanish delimiters assumed in both of the above (modify as nec.)Please Login or Register to view this content.
If you use helpers, eg:
then things are much simplified - ie use MAX and MATCH MAX to get date.Please Login or Register to view this content.
Last edited by DonkeyOte; 01-24-2011 at 05:00 AM. Reason: added helper method for sake of explanation
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
Thank you DonkeyOte,
This is good but has one problem/
If I have as following values in column B and column C is empty I get: #N/A as a result.
The result should be: 1 and the date: the first date meeting the max. which can be everydate, therefor it should return the first date ( 1/1/2008).
B
==
8
2
0
0
6
0
3
royUK,
I think such a question will never be understood without seeing the tables in the worksheet.
Al
Why not just use the helper suggestion ? More efficient and offers greater flexibility long term.
In terms of no helpers the below should work (where no MODE)
but as you can see it's hideously inefficient (that said I've no doubt there are better alternatives... perhaps MMULT based?).Please Login or Register to view this content.
As I've mentioned - a basic helper calc removes all of the above complexity.
Thank you again.
Your last formula returns the correct date.
I could not figure out which formula will return 1 as for when inly column A has what I have sown a few hours ago.
As for the helpers - I want to learn to implement complex formulas without helpers.
Al
Why ? It would be far more useful to learn efficient approaches.Originally Posted by alfredkri
If the resolution to a problem seems hideously complex then I would say that's a fair indication that you shouldn't be using it.
I confess I don't understand the question... the formula for retrieving the value associated with the date remains as before (third formula post # 3)
Your formula (post #6) resolves and returns the date but I still need a formula to return 1 for that case.
As per case post #6 - the third formula (in post # 3) returns 1 which is OK, but if I change the range into the following (which can be the case) - the formula returns 0 instead of 1 (which still is the max. of events).
B
==
0
2
0
0
6
0
3
Thanks, Al
Please post a sample with all formulae in place so as to illustrate - I for one can not replicate your issue using the data set provided (2nd Feb 2009 and 1 returned)
The count of events (post 3 formula 3) is based on the returned Date.
If the date is correct it follows the event count should follow.
There must be something that I do not understand.
So far I did not want to use any helpers and in the attached here file the date, in cell A11 is OK and it changes accordingly if I change the figures in column B.
The formula in cell B11 should return 1 but it does not.
What am I missing ?
Please attach you file with the data I present now.
Thank you,
Al
Last edited by alfredkri; 01-25-2011 at 02:35 PM.
Formula in B11
Please Login or Register to view this content.
Super!
Thank you,
Al
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks