Solved

# Excel Formula Required to Sort Data

Posted on 2011-05-06
203 Views
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
Question by:cpatte7372

LVL 6

Expert Comment

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

Kashyap,

Thanks for responding mate.

Going to check it out now....
0

Author Comment

Kashyap,

Its looking good mate.
0

LVL 6

Expert Comment

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

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

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

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

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

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
``````
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

Kash,

That makes it clear

Cheers
0

LVL 6

Expert Comment

Do close the thread if you have no further clarifications..
0

Author Closing Comment

Cheers mate..
0

## Featured Post

### Suggested Solutions

Excel file corrupted. 13 28
excel forecast function 1 27
recovering Excel 2016 file 2 24
How to Autofill Across to next value 3 20
Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
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…