# MAX and MIN in context of RANK

#### santeria

##### Well-known Member
When dealing with MAX and MIN calculations, the theoretical side of it seems straightforward, however, if you do a basic RANK formula for multiple series of numbers, is a Basic RANK formula enough? Does the use of MAX and MIN calculations from RANK figures pre-suppose that a RANK plus a COUNTIF formula is used?

Scenario at issue might be a RANK figure set that gives a result of, for example, 1,3,3,5,5 , which is the result RANK can theoretically give for a five day data-set on a Half hour range ( The double figures being figures numerically the same).

Can MAX and MIN cope with these sorts of Figures ?

Any other suggestions about trend analysis, or links gratefully received.

Ta

### Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

#### IML

##### MrExcel MVP
I'm not sure if this answers your question, but the max and min of rank set should always be 1 and n, respectively where n is the count of the numbers your are ranking. The only exception would be if all your numbers are the same, in which your min and max would both be 1 under, assuming the third argument is 0 or omitted.

#### santeria

##### Well-known Member
Thanks.
I was hoping to avoid the RANK and COUNTIF Formula at this time.

Especially when the later figures will have a wider variable.

Ta Muchly

##### MrExcel MVP
"Does the use of MAX and MIN calculations from RANK figures pre-suppose that a RANK plus a COUNTIF formula is used"

Many things are possible! If you were to give a slightly less criptic description of what your problem is, along with the expected result, something more concrete mightr emerge.

#### santeria

##### Well-known Member

Sorry about the Un-intended cryptic nature.

I am only just discovering elements such as RANK, MAX and MIN.

I am currently laying out data from week day sheets, where the Summary sheet has a series of Half Hour Break downs from 8:30am until 6:00 pm.
The Summary therefore has Monday to Friday in each Half hour slot.
Alongside of the First experiemental column are RANK, and a MAX and a MIN column.
I was toying with a combination of MAX and MIN to show Magnitude of Change, but thats not relevant for this sheet.

This first sheet is basically trying to have a semi-predictive value based on daily data.
Initially I am working with one report sheet over five days, to see what it will produce. Eventually, I may use different principal reports. However, the source data is not the real issue... the end sheet is an aim at analysis of tends in calls, and their various stats such as Talk time, Average Handle time, After Call Work, and etc... standard call centre stuff.

It is, in one sense, an attempt to define to the higher ups what an expected "normal" day is likely to be based on the figures.
In theory, I could do this for several months worth of Figures, but not at this point. The aim is a template based on a common report with RANK-ing and MAX and MIN details for each category.

I have a suspicion I am re-inventing the wheel, since I doubt that this kind of trend analysis is new in any sense. I do suspect that if a packaged item exists, it costs a fortune, and is made more for high end centres.

I guess thats a more detailed exposition. However, it may not be, since this data "mining" is mostly waffle in one sense, and the most fun part about it is finding cool macros that simplify the task, and formulas that, as well as the macros, have cross applicability to more interesting sheets of data and info ( at least for me ).

Ta

##### MrExcel MVP
"It is, in one sense, an attempt to define to the higher ups what an expected "normal" day is likely to be based on the figures"

If the stress here is on the 'expected' element, a review of some basic statistics (standard deviations, linear regression etc) would probably be more useful.

#### santeria

##### Well-known Member
Well, yes and No.

I asked my immediate boss for what I term "Red Flag Values" ... and I got a response which seems typical, and that is that I am the one who has to define the values.

Whilst the standard statistical work might be useful for understanding the actual function and events in the life of the call centre, I have to provide probable values according to what they term as values... and these are so defined, that working out actual averages, and statistical info, is practically useless.

So, at this point, I am looking at RANK, and expected MIN and MAX values for a variety of events.

However, the rubbery figures here have me so surprised, that I will eventually pull my own anomaly report... it will hopefully change one or two headache style events.

Ta

Replies
3
Views
180
Replies
1
Views
439
Replies
0
Views
264
Replies
3
Views
236
Replies
3
Views
518

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,152,322
Messages
5,769,453
Members
425,548
Latest member
macjagger17

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back