Domenic,
Thank you for your help. The first question you answered worked out great. I
am still having problems with the other two. Have have played with multiple
combonations of formulas which you sent. Examples below:
=IF(COUNT(E5:E555),INDEX(B5:B555,MATCH(LARGE(IF(MO D(ROW(E5:E555)-CELL("row",E5)+0,2)=0,E5:E555),2),E5:E555,0)),"Ope n")
This gives me a value but it's not doing every second row.
=IF(COUNT(E5:E555),INDEX(B5:B555,MATCH(LARGE(IF(MO D(ROW(E5:E555)-CELL("row",E5)+1,2)=0,E5:E555),2),E5:E555,0)),"Ope n")
This returns "#NUM!"
Could I be missing an add-on or something? What do you mean with
".... confirmed with CONTROL+SHIFT+ENTER."
Maybe I am doing something wrong. Can you help.
Thank you,
Aviator
"Domenic" wrote:
In article ,
"Aviator" wrote:
I was just given the formala below. It works great. I need to incorporate
three additional scenarios.
=INDEX(D:D,MATCH(MAX(C:C),C:C,0))
1. Can you do the same thing but if there is no values have it left blank or
return Open? (Currently, it shows #NA)
=IF(COUNT(C:C),INDEX(D:D,MATCH(MAX(C:C),C:C,0)),"O pen")
2. Can you do the same thing except just include odd numbered rows? (ie. D3,
D5.... D589)?
A3:
For every second row starting with the first cell in the range...
=MAX(IF(MOD(ROW(C3:C589)-CELL("row",C3)+0,2)=0,C3:C589))
....confirmed with CONTROL+SHIFT+ENTER.
For every second row starting with the second cell in the range...
=MAX(IF(MOD(ROW(C3:C589)-CELL("row",C3)+1,2)=0,C3:C589))
....confirmed with CONTROL+SHIFT+ENTER.
A4:
=IF(COUNT(C3:C589),INDEX(D3:D589,MATCH(A3,C3:C589, 0)),"Open")
3. Can you do the same thing except instead of the biggest date have the
second biggest date?
A3:
For every second row starting with the first cell in the range...
=LARGE(IF(MOD(ROW(C3:C589)-CELL("row",C3)+0,2)=0,C3:C589),2)
....confirmed with CONTROL+SHIFT+ENTER.
For every second row starting with the second cell in the range...
=LARGE(IF(MOD(ROW(C3:C589)-CELL("row",C3)+1,2)=0,C3:C589),2)
....confirmed with CONTROL+SHIFT+ENTER.
A4:
=IF(COUNT(C3:C589),INDEX(D3:D589,MATCH(A3,C3:C589, 0)),"Open")
Hope this helps!
|