Get highest value and adjacent cell value

A

Ardy

Formula help:
Would it be possible to get a highest of some cells and but instead of
retaining the value get the value of adjacent cell. For example, I
have numbers in cells C1 and C2 I also have Dates in Cell B1 and B2
each date corresponds to a score value. I like get a highest value
=MAX(C1:C2) and place it in C4 also like to get the adjacent date to
the highest value and place it in B3. Can this be done?

A B C
1 3/1/2006
5
2 3/5/2006 4
3
4 Date(Adjacent to highest Formula(highest)
 
A

Ardy

Hi

In C4
=MAX(C1:C2)
in B4
=INDEX(B1:B2,MATCH(C4,C1:C2,0))
Click to expand...

Roger:
Nothing happens. As if the formula is a string of text. I have
modify the cells to reflect the original spreadsheet

=INDEX(B7:B10,MATCH(F8,C7:C10,0))

B7:B10 Range of dates
C7:C10 Range of Scores
F8 The MAX(C7:C10)

OH i got a question what is 0

Ardy
 
M

Max

Ardy said:
.. Nothing happens. As if the formula is a string of text.
=INDEX(B7:B10,MATCH(F8,C7:C10,0))
Click to expand...

Your adaptation's ok and should work. From your comment: "As if the formula
is a string of text.", one possibility is that the formula cell was earlier
pre-formatted as Text (unknown to you of course).

Just reformat the formula cell to either general or number (via Format >
Cells > Number tab), then re-enter the formula by clicking inside the
formula bar and pressing ENTER. Note that you need to re-enter the formula,
re-formatting alone doesn't trigger it.
.. OH i got a question what is 0
Click to expand...

I suppose you are referring to the zero "0" (match type) within:
MATCH(F8,C7:C10,0)

A zero (match type) means to find an exact match for the lookup value F8
within the ref range C7:C10
 
A

Ardy

Your adaptation's ok and should work. From your comment: "As if the formula
is a string of text.", one possibility is that the formula cell was earlier
pre-formatted as Text (unknown to you of course).

Just reformat the formula cell to either general or number (via Format >
Cells > Number tab), then re-enter the formula by clicking inside the
formula bar and pressing ENTER. Note that you need to re-enter the formula,
re-formatting alone doesn't trigger it.


I suppose you are referring to the zero "0" (match type) within:
MATCH(F8,C7:C10,0)

A zero (match type) means to find an exact match for the lookup value F8
within the ref range C7:C10
--
Max
Singaporehttp://savefile.com/projects/236895
xdemechanik
---
Click to expand...

Max:
Thanks
 
A

Ardy

welcome. trust you got it sorted out ..
Click to expand...

OK Max:
I have another question related to the same thing. I modified the
formula to fit my application
=INDEX(C5:C8,MATCH(G6,D5:D8,0))
Works grate with one exception, The C5:C8 are date range if null you
will see 1/0/1900 I use to have this formula with help from this group
to empty the cell if there was no date:
=IF(OR(ISERROR(MAX(C5:C8)),MAX(C5:C8)=0),"",MAX(C5:C8))
How would I be able to melt your formula to this so it dose both. I
have tried couple of ways to just see what would happen but have been
unsuccessful.
 
A

Ardy

Hi

In C4
=MAX(C1:C2)
in B4
=INDEX(B1:B2,MATCH(C4,C1:C2,0))
Click to expand...

Roger:
I have another question related to the same thing. I modified the
formula to fit my application
=INDEX(C5:C8,MATCH(G6,D5:D8,0))
Works grate with one exception, The C5:C8 are date range if null you
will see 1/0/1900 I use to have this formula with help from this group
to empty the cell if there was no date:
=IF(OR(ISERROR(MAX(C5:C8)),MAX(C5:C8)=0),"",MAX(C5:C8))
How would I be able to melt your formula to this so it dose both. I
have tried couple of ways to just see what would happen but have been
unsuccessful.
 
M

Max

=INDEX(C5:C8,MATCH(G6,D5:D8,0))

My interp / guess is you just want the above to return blank: "" if the
result is zero.

If so:
=IF(INDEX(C5:C8,MATCH(G6,D5:D8,0))=0,"",INDEX(C5:C8,MATCH(G6,D5:D8,0)))
 
R

Roger Govier

Hi

I'm not sure what you are after here.
If you are saying you want a null returned, rather than Date 0
(00/01/1900) then

=IF(INDEX(C5:C8,MATCH(G6,D5:D8,0))=0,"",INDEX(C5:C8,MATCH(G6,D5:D8,0)))

Alternatively, if there is no data in column C opposite the largest
value in column D and you wanted the date next to the second highest
value, then you could use.

=IF(INDEX(C5:C8,MATCH(G6,D5:D8,0))=0,INDEX(C5:C8,MATCH(LARGE(D5:D8,2),D5:D8,0)),INDEX(C5:C8,MATCH(G6,D5:D8,0)))
 
A

Ardy

Hi

I'm not sure what you are after here.
If you are saying you want a null returned, rather than Date 0
(00/01/1900) then

=IF(INDEX(C5:C8,MATCH(G6,D5:D8,0))=0,"",INDEX(C5:C8,MATCH(G6,D5:D8,0)))

Alternatively, if there is no data in column C opposite the largest
value in column D and you wanted the date next to the second highest
value, then you could use.

=IF(INDEX(C5:C8,MATCH(G6,D5:D8,0))=0,INDEX(C5:C8,MATCH(LARGE(D5:D8,2),D5:D8,0)),INDEX(C5:C8,MATCH(G6,D5:D8,0)))
Click to expand...

Roger:
Your First assumption was correct and the formula for that works
perfect- I modify the cell numbers
=IF(INDEX(B7:B10,MATCH(F8,C7:C10,0))=0,"",INDEX(B7:B10,MATCH(F8,C7:C10,0)))

Your second one got my curiosity going and am trying to understand it
so I placed it in the situation to see what results I get so maybe by
seeing the results I would understand it better but it Keeps giving me
error(#N/A). - I modify the cell numbers
=IF(INDEX(B17:B20,MATCH(G16,C17:C20,0))=0,INDEX(B17:B20,MATCH(LARGE(C17:C20,2),C17:C20,0)),INDEX(B17:B20,MATCH(G16,C17:C20,0)))

I Thank you for the first formula

Regards
Ardy
 
R

Roger Govier

Hi

Maybe if you just moved the values down the page, then it should be
looking at cell F16 not G16
=IF(
INDEX(B17:B20,MATCH(G16,C17:C20,0))=0,
INDEX(B17:B20,MATCH(LARGE(C17:C20,2),C17:C20,0)),
INDEX(B17:B20,MATCH(F16,C17:C20,0)))

Works fine for me and returns 01/03/2006
With
B17 = 01/01/2006 C17 =5
B18 =01/03/2006 C18 =6
B19 = C19=7

F16 =MAX(C17:C20)

The formula basically says, if the first test
(checking for the Date relative to the largest value in C17:C20) =0,
then use the date opposite the second largest value in C17:C20
LARGE(C17:C20,2),
otherwise return the date opposite the largest value in C17:C20
 
A

Ardy

Hi

Maybe if you just moved the values down the page, then it should be
looking at cell F16 not G16
=IF(
INDEX(B17:B20,MATCH(G16,C17:C20,0))=0,
INDEX(B17:B20,MATCH(LARGE(C17:C20,2),C17:C20,0)),
INDEX(B17:B20,MATCH(F16,C17:C20,0)))

Works fine for me and returns 01/03/2006
With
B17 = 01/01/2006 C17 =5
B18 =01/03/2006 C18 =6
B19 = C19=7

F16 =MAX(C17:C20)

The formula basically says, if the first test
(checking for the Date relative to the largest value in C17:C20) =0,
then use the date opposite the second largest value in C17:C20
LARGE(C17:C20,2),
otherwise return the date opposite the largest value in C17:C20
Click to expand...

Thanks Roger:
I think I see what is happening now...... If you would recommend a
book that would get in to more advanced functions and formulas what
would it be.......

Regards
Ardy
 

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

Similar Threads

VBA-Table Word 2019 - Move a selected text, from a cell to the adjacent cell to its right, by pressing the ENTER key 0
Index/Match Functions to Return Concatenated Response 4
Findiing highest and lowest in rows and columns 3
Adding cells until they are greater than a lookup cell and returning a value 1
Trying to find additional ranges outside of min, max, and average 2
Drop down list control value in vba 2
Finding location (or start cell) of highest consecutive values 3
VBA Coding Help for Beginner 0

Top