A Commentary on a range of values

[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

 

[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)

 

[John]

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

 

[Leo Heuser]

John

My mistake.
Try this one instead:

=ADDRESS(MATCH(MAX(H2:H100),H2:H100,0)+ROW(H2)-1,COLUMN(H2))

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

 

[Chip Pearson]

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

=ADDRESS(ROW(C4)+MATCH(MAX(C4:C10),C4:C10,0)-1,COLUMN(C4))

where the data is in C4:C10. Adjust to fit your range.


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

 

[John]

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

 

[Frank Kabel]

Hi John
so simply tell us where your data is located

 

[John]

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

 

[Leo Heuser]

You're welcome, John.

What is your range of weekly sales?

 

[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):
=ADDRESS(MATCH(MAX(T1:T100),T1:T100,0)+ROW(T1)-1,COLUMN(T1))

in this case (if your data starts in row 19 the above could be
shortened to:
=ADDRESS(MATCH(MAX(T1:T100),T1:T100,0),COLUMN(T1))

 

[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"

 

[Leo Heuser]

Try

="Highest Weekly sales is in "&INDEX(A1:A100,MATCH(MAX(T1:T100),T1:T100,0))&
" at "&ADDRESS(MATCH(MAX(T1:T100),T1:T100,0)+ROW(T1)-1,COLUMN(T1))

 

[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

 

[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

 

[John]

Perfect, Thanks all

="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

Followup to newsgroup only please.