Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
List the last amount that is state
I have a worksheet where each month I add data and have rows Jan-Dec listed.
I add data to the worksheet each month. I want a formula where it will look at the data and the results would be the last month I have data in it. So for example, I now have Jan-Jun data listed. I want the result to be what is stated for June as that is the last month where I have data in it. If I have Jan-Sept data listed, I want the result to be what is stated in Sept. Etc. I have tried using the formula: =index(C5:C17,CountA,(C5:C17)). This works if there is an amount stated in C5:C17, but does not work if there is formulas stated in C5:C17. HELP! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
List the last amount that is state
On Mar 26, 7:59*am, PSL wrote:
I have a worksheet where each month I add data and have rows Jan-Dec listed. * I add data to the worksheet each month. *I want a formula where it will look at the data and the results would be the last month I have data in it. *So for example, I now have Jan-Jun data listed. *I want the result to be what is stated for June as that is the last month where I have data in it. *If I have Jan-Sept data listed, I want the result to be what is stated in Sept. *Etc. I have tried using the formula: =index(C5:C17,CountA,(C5:C17)). This works if there is an amount stated in C5:C17, but does not work if there is *formulas stated in C5:C17. *HELP! This is an ARRAY formuls It needs Ctrl-Shift-Enter to activate =INDIRECT("C"&MAX(IF($C$35:$C$52<0,ROW($C$35:$C$5 2)))) "C" is the column set your range |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
List the last amount that is state
=index(C5:C17,CountA,(C5:C17)).
This works if there is an amount stated in C5:C17 I take it that "amount" means the data is numeric? Try this... =IF(COUNT(C5:C17),LOOKUP(1E100,C5:C17),"") -- Biff Microsoft Excel MVP "PSL" wrote in message ... I have a worksheet where each month I add data and have rows Jan-Dec listed. I add data to the worksheet each month. I want a formula where it will look at the data and the results would be the last month I have data in it. So for example, I now have Jan-Jun data listed. I want the result to be what is stated for June as that is the last month where I have data in it. If I have Jan-Sept data listed, I want the result to be what is stated in Sept. Etc. I have tried using the formula: =index(C5:C17,CountA,(C5:C17)). This works if there is an amount stated in C5:C17, but does not work if there is formulas stated in C5:C17. HELP! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
replace state names with state code abbreviations | Excel Worksheet Functions | |||
Compare City and State to Master List | Excel Worksheet Functions | |||
Converting State Names to State Abbreviations | Excel Discussion (Misc queries) | |||
create a list box in one cell of state abbreviations | Excel Worksheet Functions | |||
US State Abbreviations List | New Users to Excel |