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)

Hi

In C4
=MAX(C1:C2)
in B4
=INDEX(B1:B2,MATCH(C4,C1:C2,0))
--
Regards

Roger Govier


"Ardy" wrote in message
oups.com...
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)

On Jul 6, 2:40 pm, "Roger Govier"
wrote:
Hi

In C4
=MAX(C1:C2)
in B4
=INDEX(B1:B2,MATCH(C4,C1:C2,0))
--
Regards

Roger Govier

"Ardy" wrote in message

oups.com...

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)

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

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

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

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
Singapore
http://savefile.com/projects/236895
xdemechanik
---

On Jul 6, 8:09 pm, "Max" wrote:
"Ardy" wrote

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

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

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

Max:
Thanks

welcome. trust you got it sorted out ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Ardy" wrote
Max:
Thanks

On Jul 6, 2:40 pm, "Roger Govier"
wrote:
Hi

In C4
=MAX(C1:C2)
in B4
=INDEX(B1:B2,MATCH(C4,C1:C2,0))
--
Regards

Roger Govier

"Ardy" wrote in message

oups.com...

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)

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.

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:C 8,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,M ATCH(LARGE(D5:D8,2),D5:D8,0)),INDEX(C5:C8,MATCH(G6 ,D5:D8,0)))

--
Regards

Roger Govier


"Ardy" wrote in message
oups.com...
On Jul 6, 2:40 pm, "Roger Govier"
wrote:
Hi

In C4
=MAX(C1:C2)
in B4
=INDEX(B1:B2,MATCH(C4,C1:C2,0))
--
Regards

Roger Govier

"Ardy" wrote in message

oups.com...

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)

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.

On Jul 13, 1:15 am, "Roger Govier"
wrote:
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:C 8,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,M ATCH(LARGE(D5:D8,2),D5:D8,0)),INDEX(C5:C8,MATCH(G6 ,D5:D8,0)))

--
Regards

Roger Govier

"Ardy" wrote in message

oups.com...

On Jul 6, 2:40 pm, "Roger Govier"
wrote:
Hi

In C4
=MAX(C1:C2)
in B4
=INDEX(B1:B2,MATCH(C4,C1:C2,0))
--
Regards

Roger Govier

"Ardy" wrote in message

groups.com...

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)

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.

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(B1 7: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

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
--
Regards

Roger Govier


"Ardy" wrote in message
ups.com...
On Jul 13, 1:15 am, "Roger Govier"
wrote:
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:C 8,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,M ATCH(LARGE(D5:D8,2),D5:D8,0)),INDEX(C5:C8,MATCH(G6 ,D5:D8,0)))

--
Regards

Roger Govier

"Ardy" wrote in message

oups.com...

On Jul 6, 2:40 pm, "Roger Govier"
wrote:
Hi

In C4
=MAX(C1:C2)
in B4
=INDEX(B1:B2,MATCH(C4,C1:C2,0))
--
Regards

Roger Govier

"Ardy" wrote in message

groups.com...

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)

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.

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(B1 7: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