Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
look up
how do I look up the last instance and calculate based on last input,
disregarding the previous input. =LOOKUP("x",B46:AF46,B1:AF1)+7 In other words, if "x" is inputted twice on consecutive rows, then calculate based on the last entry. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
look up
The following array formula will return the value in column D that correspsonds to the last value of "a" in the range C4:C10. Adjust the references to C4 and C4:C10 to the cells you have. If an "a" is not found in C4:C10, the formual returns #N/A. =IF(COUNTIF(C4:C10,"a")=0,NA(),OFFSET(C4,MAX(ROW(C 4:C10)*(C4:C10="a"))-ROW(C4),1,1,1)) This is an array formula, so you *must* press CTRL SHIFT ENTER rather than just ENTER when you first enter the formula and whenever you edit it later. If you do this properly, Excel will display the formula enclosed in curly braces { }. The formula will not return the correct result if you do not use CTRL SHIFT ENTER. See http://www.cpearson.com/Excel/arrayformulas.aspx for lots more information about array formulas. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Tue, 14 Oct 2008 12:31:00 -0700, Mrugesh Shah wrote: how do I look up the last instance and calculate based on last input, disregarding the previous input. =LOOKUP("x",B46:AF46,B1:AF1)+7 In other words, if "x" is inputted twice on consecutive rows, then calculate based on the last entry. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
look up
Chip
Thank You, for the update, however I wanted to display in column D the value that I have in row C1 through AF1, corrosponding it to in which row "a" is added. "Chip Pearson" wrote: The following array formula will return the value in column D that correspsonds to the last value of "a" in the range C4:C10. Adjust the references to C4 and C4:C10 to the cells you have. If an "a" is not found in C4:C10, the formual returns #N/A. =IF(COUNTIF(C4:C10,"a")=0,NA(),OFFSET(C4,MAX(ROW(C 4:C10)*(C4:C10="a"))-ROW(C4),1,1,1)) This is an array formula, so you *must* press CTRL SHIFT ENTER rather than just ENTER when you first enter the formula and whenever you edit it later. If you do this properly, Excel will display the formula enclosed in curly braces { }. The formula will not return the correct result if you do not use CTRL SHIFT ENTER. See http://www.cpearson.com/Excel/arrayformulas.aspx for lots more information about array formulas. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Tue, 14 Oct 2008 12:31:00 -0700, Mrugesh Shah wrote: how do I look up the last instance and calculate based on last input, disregarding the previous input. =LOOKUP("x",B46:AF46,B1:AF1)+7 In other words, if "x" is inputted twice on consecutive rows, then calculate based on the last entry. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|