How to show best and worst results over a range

  • Thread starter Thread starter Colin Hayes
  • Start date Start date
C

Colin Hayes

Hi

I have a small puzzle :

In cells B6 to B17 I have the months of the year.

In cells E6 to E17 I have a series of numerical results , one for each
month.

I'm trying in G1 and G2 to show which month has the best performance ,
and which the worst.

The results in G1 might read 'Best : July' and G2 might read 'Worst :
August'.


Can someone help with this?

Thanks.
 
In cells B6 to B17 I have the months of the year.
In cells E6 to E17 I have a series of numerical results ,
one for each month.
I'm trying in G1 and G2 to show which month has the best
performance , and which the worst.
The results in G1 might read 'Best : July' and G2 might
read 'Worst : August'.
Click to expand...

If B6:B17 contains the month name as text, then:

G1:
="Best: "
& INDEX(B6:B17,MATCH(MAX(E6:E17),E6:E17,0))

G2:
="Worst: "
& INDEX(B6:B17,MATCH(MIN(E6:E17),E6:E17,0))

If B6:B17 contains dates that you format at Custom "mmmm", then:

G1:
="Best: "
& TEXT(INDEX(B6:B17,MATCH(MAX(E6:E17),E6:E17,0)),"mmmm")

G2:
="Worst: "
& TEXT(INDEX(B6:B17,MATCH(MIN(E6:E17),E6:E17,0)),"mmmm")
 
If B6:B17 contains the month name as text, then:

G1:
="Best: "
& INDEX(B6:B17,MATCH(MAX(E6:E17),E6:E17,0))

G2:
="Worst: "
& INDEX(B6:B17,MATCH(MIN(E6:E17),E6:E17,0))

If B6:B17 contains dates that you format at Custom "mmmm", then:

G1:
="Best: "
& TEXT(INDEX(B6:B17,MATCH(MAX(E6:E17),E6:E17,0)),"mmmm")

G2:
="Worst: "
& TEXT(INDEX(B6:B17,MATCH(MIN(E6:E17),E6:E17,0)),"mmmm")
Click to expand...

HI

OK That's brilliant. Thanks.

It works perfect first time.



Best Wishes
 
If B6:B17 contains the month name as text, then:

G1:
="Best: "
& INDEX(B6:B17,MATCH(MAX(E6:E17),E6:E17,0))

G2:
="Worst: "
& INDEX(B6:B17,MATCH(MIN(E6:E17),E6:E17,0))

If B6:B17 contains dates that you format at Custom "mmmm", then:

G1:
="Best: "
& TEXT(INDEX(B6:B17,MATCH(MAX(E6:E17),E6:E17,0)),"mmmm")

G2:
="Worst: "
& TEXT(INDEX(B6:B17,MATCH(MIN(E6:E17),E6:E17,0)),"mmmm")
Click to expand...


Hi

Out of interest , how would the code be modified to also include the
numerical date from E6:E7?

To read for example :

Best : July (5678.87)

Worst : May (1095.52)


Grateful for your assistance.
 
Out of interest , how would the code be modified to
also include the numerical date from E6:E7?
To read for example :
Best : July (5678.87)
Worst : May (1095.52)
Click to expand...

="Best: "
& INDEX(B6:B17,MATCH(MAX(E6:E17),E6:E17,0))
& " (" & TEXT(MAX(E6:E17),"0.00") & ")"

="Worst: "
& INDEX(B6:B17,MATCH(MIN(E6:E17),E6:E17,0))
& " (" & TEXT(MIN(E6:E17),"0.00") & ")"
 
="Best: "
& INDEX(B6:B17,MATCH(MAX(E6:E17),E6:E17,0))
& " (" & TEXT(MAX(E6:E17),"0.00") & ")"

="Worst: "
& INDEX(B6:B17,MATCH(MIN(E6:E17),E6:E17,0))
& " (" & TEXT(MIN(E6:E17),"0.00") & ")"
Click to expand...

Hi

Yes - perfect!

Thanks again.


Are you aware that we also buy CDs, Vinyl and DVDs? Send your lists of
unwanted items to (e-mail address removed) and we’ll quote you a price…


You can browse and buy direct from my full list of items at these addresses :

http://s1.amazon.co.uk/exec/varzea/ts/my-zshop/SPJK3X6KOJZR6/026-3393902-9
050050

or:

http://www.CDandVinyl.co.uk

or :

http://www.netsoundsmusic.com/cdandvinyl

or:

http://chayes.musicstack.com



To DOWNLOAD the full catalogue click here :


http://www.chayes.demon.co.uk/CDandVinyl_full_catalogue.exe



Best Wishes ,

Colin Hayes.



TEL / FAX : (UK) (0)208 804 9181
 
Back
Top