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
 
Ad

Advertisements

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.
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.
 
C

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
 
J

John

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

Advertisements

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):
=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))
 
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"
 
Ad

Advertisements

L

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))
 
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 :)
 
Ad

Advertisements

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

Followup to newsgroup only please.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top