MAX and MIN in context of RANK

santeria

Well-known Member
Joined
Oct 7, 2003
Messages
1,844
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


(y)
 

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
Joined
Feb 15, 2002
Messages
1,743
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
Joined
Oct 7, 2003
Messages
1,844
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


(y)
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
"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
Joined
Oct 7, 2003
Messages
1,844

ADVERTISEMENT

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

(y)
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
"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
Joined
Oct 7, 2003
Messages
1,844
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

(y)
 
Master Excel Bundle

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.

Forum statistics

Threads
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?

Disable AdBlock

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
Top