To capture Highest and lowest value

[AlanSidman]

In the attached, Using Power Query, the file is set to refresh every 60 seconds. If you are linking your data to an external source this is probably the best way to achieve what you are looking for. In columns I and J the information will refresh automatically. Power Query is available in all versions of excel starting with 2010. Not available currently for a MAC.

 

[Techtrend]

We need to Short the OI from high to Low ,
Can we use rank formula to achive this

Attached is the Sheet with clarification

 

[AlanSidman]

Adding a line of code to sort the data, here is the revised Mcode

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Open", type number}, {"High", type number}, {"Low", type number}, {"Last Updated Time", type time}, {"Close", type number}, {"OI", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Maximum", each List.Max(#"Changed Type"[OI])),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Min", each List.Min(#"Changed Type"[OI])),
    #"Sorted Rows" = Table.Sort(#"Added Custom1",{{"OI", Order.Descending}})
in
    #"Sorted Rows"

 

[sujittandel]

Dear sir

Your sheet is working exactly as i want
can you tell me the step by step process to create new excel sheet like this? because i want to do the same thing in my excel sheet

 

[AlanSidman]

Read the link in my signature.

What is Power Query?

Power Query description

www.poweredsolutions.co

and

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

 

[jerry_maguire]

To Get The Largest Or Smallest Number In A Range:
Just enter the below formula into a blank cell you want to get the result:

Get the largest value: =Max (B2:F10)
Get the smallest value: =Min (B2:F10)
And then press the Enter key to get the largest or smallest number in the range.

To Get The Largest 3 Or Smallest 3 Numbers In A Range:
Please enter the below formula into a cell:

Get the largest 3 values: =LARGE(B2:F10,1)&", "&LARGE(B2:F10,2)&", "&LARGE(B2:F10,3)
Get the smallest 3 values: =SMALL(B2:F10,1)&", "&SMALL(B2:F10,2)&", "&SMALL(B2:F10,3)
The cell ranges are just an example, you can edit them accordingly.

Hope this helps,

Regards,
Jerry.

 

[sujittandel]

To Get The Largest Or Smallest Number In A Range:
Just enter the below formula into a blank cell you want to get the result:

Get the largest value: =Max (B2:F10)
Get the smallest value: =Min (B2:F10)
And then press the Enter key to get the largest or smallest number in the range.

To Get The Largest 3 Or Smallest 3 Numbers In A Range:
Please enter the below formula into a cell:

Get the largest 3 values: =LARGE(B2:F10,1)&", "&LARGE(B2:F10,2)&", "&LARGE(B2:F10,3)
Get the smallest 3 values: =SMALL(B2:F10,1)&", "&SMALL(B2:F10,2)&", "&SMALL(B2:F10,3)
The cell ranges are just an example, you can edit them accordingly.

Hope this helps,

Regards,
Jerry.

your solution is for getting large value and small value from the range, i need to capture high value and low value from dynamic data, data is continuously change in the cell ,

 

[sujittandel]

solution is already there in excel

Hi Narendra ,

See this file.

Narayan

Dear sir

Your sheet is working exactly as i want
can you tell me the step by step process to create new excel sheet like this? because i want to do the same thing in my excel sheet