# A Commentary on a range of values

J

#### John

I want towrite a set commentary on a range of values. My idea is to use the
Concatenate function bu my problem is that I want to return in part of the
commentary the location with the highest weekly sales, this obviously could
change week on week so I can't put a set cell reference within my
concatenate formula

How can I do some lookup?

Thanks

L

#### Leo Heuser

If you have weekly sales in e.g. H2:H100, this formula is
one way to do it:

="Highest weekly sales: "&MAX(H2:H100)

J

#### John

Thanks Leo, problem with that formula is that it won't return the location
name also it will just show the max value

L

#### Leo Heuser

John

My mistake.

Will only work for a vector (one row or one column). With duplicates the
address will be that of the first max-value met.

C

#### Chip Pearson

If you want the address of the maximum value, use a formula like

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

J

#### John

Thanks Leo, I'm having a bit of difficulty understand the formula in order
to place it within my range

F

#### Frank Kabel

Hi John
so simply tell us where your data is located

J

#### John

Data is in Column T with the location that relates to that data in Column A

L

#### Leo Heuser

You're welcome, John.

What is your range of weekly sales?

F

#### Frank Kabel

Hi
still a little bit light on detail. so your values are in column
T1:T100 for example. In this case use the following adaption (not sure
what you have in column A):

in this case (if your data starts in row 19 the above could be
shortened to:

J

#### John

Sales are in Column T and Location names are in column A eg

England (Col A) e1750 (Col T)
Holland (Col A) e6790 (Col T)
Spain (Col A) e700 (Col T)

Thus my concatenate formula should say "Highest Weekly sales is in Holland
at e6790"

L

#### Leo Heuser

Try

="Highest Weekly sales is in "&INDEX(A1:A100,MATCH(MAX(T1:T100),T1:T100,0))&

J

#### John

Thanks again Leo, this formula partly works except for the fact it returns
the coordinate of the highest value and not the ACTUAL highest value

L

#### Leo Heuser

="Highest Weekly sales is in
"&INDEX(A1:A100,MATCH(MAX(T1:T100),T1:T100,0))&
" at "&MAX(T1:T100)

Problem was, that when you talked about location, you meant
country and not location (address) of maxvalue.
Anyway, it should work now

J

#### John

Perfect, Thanks all

Leo Heuser said:
="Highest Weekly sales is in
"&INDEX(A1:A100,MATCH(MAX(T1:T100),T1:T100,0))&
" at "&MAX(T1:T100)

Problem was, that when you talked about location, you meant
country and not location (address) of maxvalue.
Anyway, it should work now

--
Best Regards
Leo Heuser