Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Index & Counta formula problem
My formula returns last value in column of numbers. If none of the cells are blank between first entry and last entry,th
formula works. But if one cell is blank formula returns last entry before blank cell. How can i modify formula to displa last entry even if their is a blank cell EXAMPLE b35 4 b36 5 b37 5 b3 b3 In above example formula returns 55 which is correct b35 4 b36 b37 5 b3 b3 In above example returns 45, I need it to return 55 as that is last entry Following is my formula =INDEX(B35:B39,COUNTA(B35:B39),1 Thank Bob Leonar |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Index & Counta formula problem
it's not the cleanest way to do it, but the only way i know
how to do this is if you know what the maximum number of rows you have is. try this: = Range("B50").End(xlUP).Value where the "B50" cell is further down than the rows you have. how this works is that you give it a cell to start at and excel works its way up the culumn from the bottom cell you provide until it finds the first non-empty cell. hope that helps -----Original Message----- My formula returns last value in column of numbers. If none of the cells are blank between first entry and last entry,the formula works. But if one cell is blank formula returns last entry before blank cell. How can i modify formula to display last entry even if their is a blank cell? EXAMPLE: b35 45 b36 50 b37 55 b38 b39 In above example formula returns 55 which is correct! b35 45 b36 b37 55 b38 b39 In above example returns 45, I need it to return 55 as that is last entry! Following is my formula: =INDEX(B35:B39,COUNTA(B35:B39),1) Thanks Bob Leonard . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Index & Counta formula problem
Bob,
Try this =INDEX(B:B,MAX(IF(B35:B39<"",ROW(35:39)))) it's an array formula, so commit with Ctrl-Shift-Enter -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "rleonard" wrote in message ... My formula returns last value in column of numbers. If none of the cells are blank between first entry and last entry,the formula works. But if one cell is blank formula returns last entry before blank cell. How can i modify formula to display last entry even if their is a blank cell? EXAMPLE: b35 45 b36 50 b37 55 b38 b39 In above example formula returns 55 which is correct! b35 45 b36 b37 55 b38 b39 In above example returns 45, I need it to return 55 as that is last entry! Following is my formula: =INDEX(B35:B39,COUNTA(B35:B39),1) Thanks Bob Leonard |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Index & Counta formula problem
One way might be to use MATCH
Assuming all values in B are greater than 0 =INDEX(B35:B39, MATCH(0, B35:B39, -1), 1) -- Rob van Gelder - http://www.vangelder.co.nz/excel "rleonard" wrote in message ... My formula returns last value in column of numbers. If none of the cells are blank between first entry and last entry,the formula works. But if one cell is blank formula returns last entry before blank cell. How can i modify formula to display last entry even if their is a blank cell? EXAMPLE: b35 45 b36 50 b37 55 b38 b39 In above example formula returns 55 which is correct! b35 45 b36 b37 55 b38 b39 In above example returns 45, I need it to return 55 as that is last entry! Following is my formula: =INDEX(B35:B39,COUNTA(B35:B39),1) Thanks Bob Leonard |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Index & Counta formula problem
=LOOKUP(9.99999999999999E+307,B:B)
"rleonard" wrote in message ... My formula returns last value in column of numbers. If none of the cells are blank between first entry and last entry,the formula works. But if one cell is blank formula returns last entry before blank cell. How can i modify formula to display last entry even if their is a blank cell? EXAMPLE: b35 45 b36 50 b37 55 b38 b39 In above example formula returns 55 which is correct! b35 45 b36 b37 55 b38 b39 In above example returns 45, I need it to return 55 as that is last entry! Following is my formula: =INDEX(B35:B39,COUNTA(B35:B39),1) Thanks Bob Leonard |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Index & Counta formula problem
Bob
Try =IF(ISERROR(MATCH(,B35:B39,-1)),"No Sources",INDEX(B35:B39, MATCH(0, B35:B39, -1), 1)) -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "rleonard" wrote in message ... Rob, If all entries are blank, how can I change formula so it does not return #N/A Formula worked once I began entering entries in cells. Instead of returing #N/A could it return "No Scores" Thanks Bob Leonard |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Index & Counta formula problem
yet another solution:
Sub checklv() With Worksheets("sheet1") lv = .Cells(Rows.Count, "A").End(xlUp).Value MsgBox "lastvalue = " & lv End With End Sub Sal |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cell color index formula reference instead of =counta | Excel Discussion (Misc queries) | |||
CountA problem | Excel Discussion (Misc queries) | |||
Problem with INDEX formula | Excel Discussion (Misc queries) | |||
Index formula problem | Excel Worksheet Functions | |||
Array Formula Index Match formulas in VBA problem | Excel Programming |