?
Solved

Excel Formula Required to Sort Data

Posted on 2011-05-06
12
Medium Priority
?
251 Views
Last Modified: 2012-05-11
Hello Experts,

In the past an expert called Dave helped me with a formula to assess excel numbers based on certain conditions, he is probably asleep so I wonder if another expert could assist me with this request.

I'm looking to sort data based on the following, (as illustrated in the attached spreadsheet)

Condition 1
On the 20th day I would like establish if the low cell CB2 (93.4) was the lowest price for past twenty days - I think this can be achieved with =MIN(C2:CC2), but you're the experts - True/False

Condition 2
Is CD > CA True/False

Condition 3
Is CH > CE & CH > CD - True/False

Condition 4
Is CL > CI & CL > CH - True/False

If all four conditions are true or false I would like the result shown in column CR.

Finally, I would like a conditional format in cell A2 which will be highlighted if any of the results in column CR are true.

I've tried to explain myself as best I can. However, if you need further clarification to help me with my request, please let me know

Cheers
EE20DayLow.xlsm
0
Comment
Question by:cpatte7372
  • 7
  • 5
12 Comments
 
LVL 6

Expert Comment

by:theKashyap
ID: 35704424
See attached. If this what you're looking for?
PS: As none of the values in CR are Yes, I've made row 3 as "Yes" just to show the highlighting. ee20.xls
0
 

Author Comment

by:cpatte7372
ID: 35704471
Kashyap,

Thanks for responding mate.

Going to check it out now....
0
 

Author Comment

by:cpatte7372
ID: 35704509
Kashyap,

Its looking good mate.
0
[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

 
LVL 6

Expert Comment

by:theKashyap
ID: 35704538
Sure..
Plz do check the formulas as not a single row matched all four conditions. I'm not so much into stocks so don't really understand what you're trying to do.. :-)
0
 

Author Comment

by:cpatte7372
ID: 35704683
Kashyap,

I'm not getting a true statements, therefore I think I'm going to have broaden my search.

Could you please show me how to make the first condition be the lowest price on either the 18th, 19th and 20th day.

At the moment, the formula checks to see if the price is the lowest on the 20th day. I would also like a true statement if the lowest price on day 19 (92.62) is the lowest price between day one and day 19. And the same for day 18.

I hope that makes sense.

Cheers
0
 
LVL 6

Expert Comment

by:theKashyap
ID: 35704798
Formula "=IF(MIN(C2:BU2) = BU2, 1, 0)" would tell you if day 18's low was lowest during period day1 to day18 or not.
Formula "=IF(MIN(C2:BY2) = BY2, 1, 0)" would tell you if day 19's low was lowest during period day1 to day19 or not.
Attached one has 2 new columns, one each for day 18 and 19. Formula is on same lines as the day 20.
ee20.xls

Perhaps if you tell me what you're trying to do I can do more..
0
 

Author Comment

by:cpatte7372
ID: 35704862
Kashyap,

That appears to be working fine, however now that you have kindly made the changes I now need to changes to reflect column CT.

At the moment I'll get a yes/no if condition 1 on day 20 through CS2 is all 1 or 0's. However, I would now need a yes/no if day 19 and condition 2, 3, & 4 match, same for day 18.

Again, I hope I'm making sense.....
0
 

Author Comment

by:cpatte7372
ID: 35704900
Kash,

I was just wondering if it was possible to have an OR statement in the formula?

For example, in condition 3 you have the following:

=IF(CH2>CE2, IF(CH2>CD2, 1, 0), 0)

I was wondering if you could have OR statement attached that formula which simply says OR just CH2 > CD2?

Cheers
0
 
LVL 6

Accepted Solution

by:
theKashyap earned 2000 total points
ID: 35704948
Yes, it's possible.
=IF(OR(CH2>CE2, CH2>CD2), 1, 0)
This formula means:
if CH2>CE2 OR CH2>CD2 then
   value = 1
else
   value = 0
end if

Open in new window

Is that what you're looking for?

If it helps you understand the IF, OR and AND better.
This formula:
=IF(CH2>CE2, IF(CH2>CD2, 1, 0), 0)
is same as:
=IF(AND(CH2>CE2, CH2>CD2), 1, 0)
0
 

Author Comment

by:cpatte7372
ID: 35705066
Kash,

That makes it clear

Cheers
0
 
LVL 6

Expert Comment

by:theKashyap
ID: 35717611
Do close the thread if you have no further clarifications..
0
 

Author Closing Comment

by:cpatte7372
ID: 35773724
Cheers mate..
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

593 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question