Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 07-24-2017, 07:56 AM
FarStar FarStar is offline Need help with an IF formula, which is based on adjacent cells being in the Top 30% of a range Windows 10 Need help with an IF formula, which is based on adjacent cells being in the Top 30% of a range Office 2016
Novice
Need help with an IF formula, which is based on adjacent cells being in the Top 30% of a range
 
Join Date: Jul 2017
Posts: 10
FarStar is on a distinguished road
Default 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.
Reply With Quote
  #2  
Old 07-24-2017, 09:48 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is online now Need help with an IF formula, which is based on adjacent cells being in the Top 30% of a range Windows 7 64bit Need help with an IF formula, which is based on adjacent cells being in the Top 30% of a range Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

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
Reply With Quote
  #3  
Old 07-24-2017, 10:17 AM
FarStar FarStar is offline Need help with an IF formula, which is based on adjacent cells being in the Top 30% of a range Windows 10 Need help with an IF formula, which is based on adjacent cells being in the Top 30% of a range Office 2016
Novice
Need help with an IF formula, which is based on adjacent cells being in the Top 30% of a range
 
Join Date: Jul 2017
Posts: 10
FarStar is on a distinguished road
Default

Quote:
Originally Posted by Pecoflyer View Post
Hi and welcome
could you please post a sample sheet ( no pics please) showing some date and desired results?
Thx
6/13/2016 9:35 AM $15.18
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.
Reply With Quote
  #4  
Old 07-24-2017, 11:22 PM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is online now Need help with an IF formula, which is based on adjacent cells being in the Top 30% of a range Windows 7 64bit Need help with an IF formula, which is based on adjacent cells being in the Top 30% of a range Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

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
Reply With Quote
  #5  
Old 07-25-2017, 04:10 AM
FarStar FarStar is offline Need help with an IF formula, which is based on adjacent cells being in the Top 30% of a range Windows 10 Need help with an IF formula, which is based on adjacent cells being in the Top 30% of a range Office 2016
Novice
Need help with an IF formula, which is based on adjacent cells being in the Top 30% of a range
 
Join Date: Jul 2017
Posts: 10
FarStar is on a distinguished road
Default

Quote:
Originally Posted by Pecoflyer View Post
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.
My apologies. I don't understand what you mean when you say you want "a sheet".
Reply With Quote
  #6  
Old 07-25-2017, 05:11 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is online now Need help with an IF formula, which is based on adjacent cells being in the Top 30% of a range Windows 7 64bit Need help with an IF formula, which is based on adjacent cells being in the Top 30% of a range Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

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
Reply With Quote
  #7  
Old 07-25-2017, 05:50 AM
FarStar FarStar is offline Need help with an IF formula, which is based on adjacent cells being in the Top 30% of a range Windows 10 Need help with an IF formula, which is based on adjacent cells being in the Top 30% of a range Office 2016
Novice
Need help with an IF formula, which is based on adjacent cells being in the Top 30% of a range
 
Join Date: Jul 2017
Posts: 10
FarStar is on a distinguished road
Default

Quote:
Originally Posted by Pecoflyer View Post
An Excel worksheet
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.
Reply With Quote
  #8  
Old 07-25-2017, 08:37 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is online now Need help with an IF formula, which is based on adjacent cells being in the Top 30% of a range Windows 7 64bit Need help with an IF formula, which is based on adjacent cells being in the Top 30% of a range Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

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
Reply With Quote
  #9  
Old 07-25-2017, 08:42 AM
FarStar FarStar is offline Need help with an IF formula, which is based on adjacent cells being in the Top 30% of a range Windows 10 Need help with an IF formula, which is based on adjacent cells being in the Top 30% of a range Office 2016
Novice
Need help with an IF formula, which is based on adjacent cells being in the Top 30% of a range
 
Join Date: Jul 2017
Posts: 10
FarStar is on a distinguished road
Default

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.
Attached Files
File Type: xlsx Test 1.xlsx (80.1 KB, 9 views)
Reply With Quote
  #10  
Old 07-25-2017, 09:02 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is online now Need help with an IF formula, which is based on adjacent cells being in the Top 30% of a range Windows 7 64bit Need help with an IF formula, which is based on adjacent cells being in the Top 30% of a range Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

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
Reply With Quote
  #11  
Old 07-25-2017, 09:03 AM
FarStar FarStar is offline Need help with an IF formula, which is based on adjacent cells being in the Top 30% of a range Windows 10 Need help with an IF formula, which is based on adjacent cells being in the Top 30% of a range Office 2016
Novice
Need help with an IF formula, which is based on adjacent cells being in the Top 30% of a range
 
Join Date: Jul 2017
Posts: 10
FarStar is on a distinguished road
Default

Quote:
Originally Posted by Pecoflyer View Post
Perfect. Thank you and sorry for my bad explanation
It's good. Thank you kindly.
Reply With Quote
  #12  
Old 07-25-2017, 07:34 PM
xor xor is offline Need help with an IF formula, which is based on adjacent cells being in the Top 30% of a range Windows 10 Need help with an IF formula, which is based on adjacent cells being in the Top 30% of a range Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,097
xor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to all
Default

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 ...?
Reply With Quote
  #13  
Old 07-25-2017, 08:25 PM
FarStar FarStar is offline Need help with an IF formula, which is based on adjacent cells being in the Top 30% of a range Windows 10 Need help with an IF formula, which is based on adjacent cells being in the Top 30% of a range Office 2016
Novice
Need help with an IF formula, which is based on adjacent cells being in the Top 30% of a range
 
Join Date: Jul 2017
Posts: 10
FarStar is on a distinguished road
Default

Quote:
Originally Posted by xor View Post
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 ...?
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.
Reply With Quote
  #14  
Old 07-25-2017, 10:04 PM
xor xor is offline Need help with an IF formula, which is based on adjacent cells being in the Top 30% of a range Windows 10 Need help with an IF formula, which is based on adjacent cells being in the Top 30% of a range Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,097
xor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to all
Default

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.
Attached Files
File Type: xlsx Percentile.xlsx (192.9 KB, 8 views)
Reply With Quote
  #15  
Old 07-26-2017, 06:31 AM
FarStar FarStar is offline Need help with an IF formula, which is based on adjacent cells being in the Top 30% of a range Windows 10 Need help with an IF formula, which is based on adjacent cells being in the Top 30% of a range Office 2016
Novice
Need help with an IF formula, which is based on adjacent cells being in the Top 30% of a range
 
Join Date: Jul 2017
Posts: 10
FarStar is on a distinguished road
Default

Quote:
Originally Posted by xor View Post
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.
This is brilliant. Thank you very much! However, I just realized it doesn't provide me with what it is that I ultimately need... the time of days that the x's in Column E refer to. I need to pull the top 30% time of day results, while the x provides the Top 30%, I'd still have to copy and paste the Time of Day into a column.

I'm VERY grateful... I hope this doesn't seem any different to point out I'm still not where I need to be.
Reply With Quote
Reply

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
Need help with an IF formula, which is based on adjacent cells being in the Top 30% of a range 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
Need help with an IF formula, which is based on adjacent cells being in the Top 30% of a range Sum Formula in the range with Numeric and NonNumeric data cells Spanec Excel 2 01-12-2012 09:15 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 10:06 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft