Hi,
I got a problem here. I would like to create a signal for the top 5 hours which has the highest volume
As you can see from the chart or picture. I want to create a =IF function signal so that on the right column it will show which of the hours is the top 20%/top 5 hours
Is there anyway possible in excel to do this? To create a signal on the right showing which of the days hours, is the top 5 hours with the highest volumne?
Thanks alot in advance guys
Last edited by andrian; 09-18-2009 at 08:42 PM.
Do you mean ?
or another way:
Please Login or Register to view this content.
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
I used
=IF((G2/MAX($G$2:$G$24))>=80%,"Top 20%","")
thanks!!! all worked perfectly fine
one more thing
this is only for 1 day, i've like 1000 days
how do i do it in such a way that i can do the formula for all the days?
thanks in advance again guys
For your given example I change A2:A14 to 1 date and A15:A24 to another.
The used this Array formula, commit using CTRL+SHIFT+ENTER.
=IF((G2:G24/MAX(IF($A$2:$A$24=A2,$G$2:$G$24,0)))>=80%,"Top 20%","")
Thanks Andy for the reply
Like i said i have about 1000 days so i can possibly manually do it, well i can, but its gonna take too long
Can you please explain how to work this array formula? Ctrl Shift Enter? Im not too familiar with it
Ive attached another file with a more days
Thanks in advance again
in H1 use the formula
=IF((G1:G311/MAX(IF($A$1:$A$311=A1,$G$1:$G$311,0)))>=80%,"Top 20%","")
the rather than simply pressing RETRUN to enter the formula use CTRL+SHIFT+ENTER
This will make the formula an Array formula. It will also add { } brackets to the formula when displayed in the formula bar.
In the attached I have added some formula to illustrate what the formula is doing so you can see the items with +80% values.
Given the dates are seemingly sorted in ascending order you could avoid Array by means of INDEX in the MAX call, eg adapting Andy's last formula:
the above should perform better than the Array in the Long Term.Please Login or Register to view this content.
Thank you for your patience and help
I've tried the last method with the index, however the results came out abit weird.
For some days, there were more than 5 hours that were considered in the top 20%, i would assume the top 20% would only be 4-5 hours?
Some days only have 3 hours which are in the top 20%
I've highlighted the days which seems wrong.
What is your take on this?
I believe Andy's approach is based on values being within 20% of the MAX value (seems sensible approach to me in truth).
Sorry for the misunderstanding
When I said top 20%, I meant the top 5 hours of the day
Would it be possible for it to only indicate the top 5 hours with the highest volume?
Reverting then to the earlier LARGE approach but still differentiating by day based on INDEX approach (rather than Array)
Does that work for you ?Please Login or Register to view this content.
(note the addition of the COUNTIF in the MIN on the off chance there are fewer than 5 records for a given day)
On an aside, without being quite clear as to where this is leading, given top 5 requirement a Pivot Table based approach might be of interest ? (see attached)
Note also if preferred you can sort the PT such that times are listed in order of Volume (Asc/Desc)
Thank you all, to DonkeyOte and Andypope
The 2nd last formula worked like a charm!
Really really grateful, i should have asked this forum earlier rather than spend one whole day wrecking my brains out
thanks again!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks