|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Need help with an IF formula, which is based on adjacent cells being in the Top 30% of a range
Hi all. Newbie here. I need help figuring out a formula. Column A has dates, Column B shows time of day (5 minute intervals), & Column C has $ amounts. I need Column D to show an "x" if the adjacent cell in Column C (dollar amounts) falls within the Top 30% by date.
Hope I worded this right. Thanks you. |
#2
|
||||
|
||||
Hi and welcome
could you please post a sample sheet ( no pics please) showing some date and desired results? Thx
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#3
|
|||
|
|||
Quote:
6/13/2016 9:40 AM $15.16 6/13/2016 9:45 AM $15.07 6/13/2016 9:50 AM $15.06 6/13/2016 9:55 AM $15.13 6/13/2016 10:00 AM $15.14 6/13/2016 10:05 AM $15.14 6/13/2016 10:10 AM $15.13 6/13/2016 10:15 AM $15.14 6/13/2016 10:20 AM $15.16 6/13/2016 10:25 AM $15.16 6/13/2016 10:30 AM $15.16 6/13/2016 10:35 AM $15.17 6/13/2016 10:40 AM $15.18 6/13/2016 10:45 AM $15.12 6/13/2016 10:50 AM $15.13 6/13/2016 10:55 AM $15.14 6/13/2016 11:00 AM $15.20 6/13/2016 11:05 AM $15.18 6/13/2016 11:10 AM $15.19 6/13/2016 11:15 AM $15.19 6/13/2016 11:20 AM $15.19 6/13/2016 11:25 AM $15.20 6/13/2016 11:30 AM $15.19 6/13/2016 11:35 AM $15.17 6/13/2016 11:40 AM $15.15 6/13/2016 11:45 AM $15.15 6/13/2016 11:50 AM $15.15 6/13/2016 11:55 AM $15.13 6/13/2016 12:00 PM $15.13 6/13/2016 12:05 PM $15.12 6/13/2016 12:10 PM $15.12 6/13/2016 12:15 PM $15.12 6/13/2016 12:20 PM $15.12 6/13/2016 12:25 PM $15.12 6/13/2016 12:35 PM $15.13 6/13/2016 12:40 PM $15.12 6/13/2016 12:45 PM $15.12 6/13/2016 12:50 PM $15.12 6/13/2016 12:55 PM $15.11 6/13/2016 1:05 PM $15.11 6/13/2016 1:10 PM $15.11 6/13/2016 1:15 PM $15.11 6/13/2016 1:20 PM $15.11 6/13/2016 1:25 PM $15.11 6/13/2016 1:30 PM $15.12 6/13/2016 1:35 PM $15.12 6/13/2016 1:40 PM $15.10 6/13/2016 1:45 PM $15.08 6/13/2016 1:50 PM $15.11 6/13/2016 1:55 PM $15.11 6/13/2016 2:00 PM $15.09 6/13/2016 2:05 PM $15.10 6/13/2016 2:10 PM $15.10 6/13/2016 2:15 PM $15.10 6/13/2016 2:20 PM $15.10 6/13/2016 2:25 PM $15.09 6/13/2016 2:35 PM $15.10 6/13/2016 2:40 PM $15.12 6/13/2016 2:45 PM $15.12 6/13/2016 2:50 PM $15.14 6/13/2016 2:55 PM $15.12 6/13/2016 3:00 PM $15.12 6/13/2016 3:05 PM $15.11 6/13/2016 3:10 PM $15.11 6/13/2016 3:15 PM $15.09 6/13/2016 3:20 PM $15.09 6/13/2016 3:25 PM $15.09 6/13/2016 3:30 PM $15.08 6/13/2016 3:35 PM $15.08 6/13/2016 3:40 PM $15.07 6/13/2016 3:45 PM $15.05 6/13/2016 3:50 PM $15.05 6/13/2016 3:55 PM $15.05 6/13/2016 4:00 PM $15.05 6/20/2016 9:35 AM $16.31 6/20/2016 9:40 AM $16.35 6/20/2016 9:45 AM $16.34 6/20/2016 9:50 AM $16.37 6/20/2016 9:55 AM $16.36 6/20/2016 10:00 AM $16.34 6/20/2016 10:05 AM $16.34 6/20/2016 10:10 AM $16.38 6/20/2016 10:15 AM $16.38 6/20/2016 10:20 AM $16.38 6/20/2016 10:25 AM $16.39 6/20/2016 10:30 AM $16.37 6/20/2016 10:35 AM $16.36 6/20/2016 10:40 AM $16.36 6/20/2016 10:45 AM $16.37 6/20/2016 10:50 AM $16.36 6/20/2016 10:55 AM $16.40 6/20/2016 11:00 AM $16.39 6/20/2016 11:05 AM $16.38 6/20/2016 11:10 AM $16.38 6/20/2016 11:15 AM $16.38 6/20/2016 11:20 AM $16.38 6/20/2016 11:25 AM $16.38 6/20/2016 11:30 AM $16.42 6/20/2016 11:35 AM $16.42 6/20/2016 11:40 AM $16.41 6/20/2016 11:45 AM $16.41 6/20/2016 11:50 AM $16.41 6/20/2016 11:55 AM $16.40 6/20/2016 12:00 PM $16.39 6/20/2016 12:05 PM $16.41 6/20/2016 12:10 PM $16.41 6/20/2016 12:15 PM $16.40 6/20/2016 12:20 PM $16.40 6/20/2016 12:25 PM $16.39 6/20/2016 12:30 PM $16.39 6/20/2016 12:35 PM $16.38 6/20/2016 12:40 PM $16.38 6/20/2016 12:45 PM $16.39 6/20/2016 12:50 PM $16.38 6/20/2016 12:55 PM $16.40 6/20/2016 1:00 PM $16.40 6/20/2016 1:10 PM $16.41 6/20/2016 1:15 PM $16.40 6/20/2016 1:20 PM $16.38 6/20/2016 1:25 PM $16.37 6/20/2016 1:30 PM $16.38 6/20/2016 1:35 PM $16.37 6/20/2016 1:40 PM $16.38 6/20/2016 1:45 PM $16.38 6/20/2016 1:50 PM $16.39 6/20/2016 1:55 PM $16.39 6/20/2016 2:00 PM $16.38 6/20/2016 2:05 PM $16.37 6/20/2016 2:10 PM $16.37 6/20/2016 2:15 PM $16.36 6/20/2016 2:20 PM $16.36 6/20/2016 2:25 PM $16.35 6/20/2016 2:30 PM $16.35 6/20/2016 2:35 PM $16.35 6/20/2016 2:40 PM $16.35 6/20/2016 2:45 PM $16.35 6/20/2016 2:50 PM $16.35 6/20/2016 2:55 PM $16.36 6/20/2016 3:00 PM $16.36 6/20/2016 3:05 PM $16.36 6/20/2016 3:10 PM $16.35 6/20/2016 3:15 PM $16.33 6/20/2016 3:20 PM $16.32 6/20/2016 3:25 PM $16.32 6/20/2016 3:30 PM $16.32 6/20/2016 3:35 PM $16.32 6/20/2016 3:40 PM $16.31 6/20/2016 3:45 PM $16.31 6/20/2016 3:50 PM $16.30 6/20/2016 3:55 PM $16.30 6/20/2016 4:00 PM $16.30 Sorry about the columns being too close. A is Date, B is time, and C is Amount. I'd like Column D to have an x in it if the adjacent data in Column C is within the Top 30% of value for the date. |
#4
|
||||
|
||||
It would be much easier if you had posted a sheet as requested.
As I and other members do not have the same regional settings it will take copy pasting the data will require tweaking to get dates and times OK.
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#5
|
|||
|
|||
My apologies. I don't understand what you mean when you say you want "a sheet".
|
#6
|
||||
|
||||
An Excel worksheet
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#7
|
|||
|
|||
Ah... thank you. Here goes.
https://docs.google.com/spreadsheets...it?usp=sharing Ultimately, I'm trying to figure out what time of day is the most likely to have the highest prices. I conditional formatted my private file to show me the top 30% of each day, yet I have to then manually copy the adjacent time of day into the D column and that takes a long time for five years of data. I figured an X or True or something similar in D would allow me to sort D and get the times of day I want. Hope this makes sense. |
#8
|
||||
|
||||
I think I have put my question badly.
It is best to post your sheet on the forum ( click "Go advanced" - Manage attachments) External links can be unsafe and some members may not have access to them, depriving you from valuable help. Thx
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#9
|
|||
|
|||
Ok... hope this is what you meant.
The file shows the conditionally formatted Top Ranked values of 30% highlighted in green for each day. My goal is to have a way to note the time of day in the cell in column D. In other words, having it tell me 10:25 AM in D12. Having an X or Y or something else works as well, because I can then simply sort D and then copy and paste the times into column E. I already created a formula that would count the times of day and tell me which times occur the most, but I need the data first. Thanks. |
#10
|
||||
|
||||
Perfect. Thank you and sorry for my bad explanation
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#11
|
|||
|
|||
It's good. Thank you kindly.
|
#12
|
|||
|
|||
Why isn't there an equal number of prices per day (5 minute intervals). For 6/20/2016 in your file there are 77 prices, for 6/27/2016 there are 77, for 7/11/2016 there are 74 prices, next 77, 76, 78 ...?
|
#13
|
|||
|
|||
The data comes from a stock firm and some times there are glitches with times missing. I'd prefer it to be equal, but it's not always the case, unfortunately. Regardless, I'm still hoping someone finds a solution for me. Thank you.
|
#14
|
|||
|
|||
I have attached a file which might be of some interest although it is awfully slow (with about 3500 rows). With 18000 rows as in your Google spreadsheet I guess it will kill Excel completely.
Columns H:M are helper columns. |
#15
|
|||
|
|||
Quote:
I'm VERY grateful... I hope this doesn't seem any different to point out I'm still not where I need to be. |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Distribute text in one cell across a range of cells (overcoming selection.range.cells.count bug) | slaycock | Word VBA | 0 | 02-18-2017 07:00 AM |
Conditional formating all cells in an array based on adjacent cells | deejay | Excel | 6 | 12-20-2016 12:00 PM |
Excel Formula: return a range of cells that match | tinfanide | Excel | 4 | 08-30-2014 07:03 AM |
Color-fill a range of cells, based on text in a different sheet. Possible? | unittwentyfive | Excel | 2 | 06-01-2014 06:48 AM |
Sum Formula in the range with Numeric and NonNumeric data cells | Spanec | Excel | 2 | 01-12-2012 09:15 AM |