A Commentary on a range of values

  • Thread starter Thread starter John
  • Start date Start date
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
 
If you have weekly sales in e.g. H2:H100, this formula is
one way to do it:

="Highest weekly sales: "&MAX(H2:H100)
 
Thanks Leo, problem with that formula is that it won't return the location
name also it will just show the max value
 
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.
 
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
 
Thanks Leo, I'm having a bit of difficulty understand the formula in order
to place it within my range
 
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))
 
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"
 
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))
 
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
 
="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 :-)
 
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

Followup to newsgroup only please.
Click to expand...
 
Back
Top