Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup/count blank cells problem
Hello,
I have used the LOOKUP formula to find the value of the the next cell up with data (because there are blank lines seperating them), and this works. But now I need to divide the difference of two values by the number of blank lines that seperates them plus 1 (the number of days that the two values are apart). COUNTBLANK will not work because it will return the amount of blank cells total in a column, not just ones that are seperating two values (which are not always in the same cells). Example: This month there happens to be a value 101,010 in C5 and later a value 102,020 in C12. In D12 I would like the difference (102,020-101,010) divided by the number of cells apart (C12-C5=7). Thanks, Tom |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup/count blank cells problem
You should be able to specify a range with the COUNTBLANK function.
in D12, try this formula: =(C12-C5)/COUNTBLANK(C5:C12) "Tom Watt" wrote: Hello, I have used the LOOKUP formula to find the value of the the next cell up with data (because there are blank lines seperating them), and this works. But now I need to divide the difference of two values by the number of blank lines that seperates them plus 1 (the number of days that the two values are apart). COUNTBLANK will not work because it will return the amount of blank cells total in a column, not just ones that are seperating two values (which are not always in the same cells). Example: This month there happens to be a value 101,010 in C5 and later a value 102,020 in C12. In D12 I would like the difference (102,020-101,010) divided by the number of cells apart (C12-C5=7). Thanks, Tom |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup/count blank cells problem
The problem is specifying the cells each month. One month values may
exist in C5 and C12, the next month they may be in C4 and C13. I would like to create one set of formulas, lock them and they work.. Thanks, Tom JLatham wrote: You should be able to specify a range with the COUNTBLANK function. in D12, try this formula: =(C12-C5)/COUNTBLANK(C5:C12) "Tom Watt" wrote: Hello, I have used the LOOKUP formula to find the value of the the next cell up with data (because there are blank lines seperating them), and this works. But now I need to divide the difference of two values by the number of blank lines that seperates them plus 1 (the number of days that the two values are apart). COUNTBLANK will not work because it will return the amount of blank cells total in a column, not just ones that are seperating two values (which are not always in the same cells). Example: This month there happens to be a value 101,010 in C5 and later a value 102,020 in C12. In D12 I would like the difference (102,020-101,010) divided by the number of cells apart (C12-C5=7). Thanks, Tom |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup/count blank cells problem
I couldn't find/figure out a worksheet function that does the coding
equivalent of ..End(xlUp).Row perhaps someone will provide one. If I had that, then I could have done this without a UDF (User Defined Function). But I created a UDF (macro function) and by using it, I have created this monstrosity that I think will do what you want with a couple of "watch out for's". First, the User Defined Function code: Function CBA(CellAddress As String) 'CBA = Counts Blanks Above 'cell referenced as the CellAddress Dim EndRow As Long Dim CurrentAddress As String CurrentAddress = CellAddress ' black box it EndRow = Range(CurrentAddress).End(xlUp).Row CBA = EndRow - Range(CurrentAddress).Row End Function Now for the really fun part - the formula to place into the cell where you want the calculations to be performed. Assuming a layout where Row 1 on the sheet contains column headers/titles like: A B ...other column labels 1 Quantity Average ....other column titles/headers 2 7 3 4 5 8 then in column B, Row 3 (1 row down from 1st real data entry in column A) enter this formula: =IF(OFFSET(B3,0,-1)0,(OFFSET(B3,0,-1)+ OFFSET(B3,(CBA(ADDRESS(ROW(B3),COLUMN(A2)))),-1))/IF((ABS(CBA(ADDRESS(ROW(B3),COLUMN(A2))))-1)0,(ABS(CBA(ADDRESS(ROW(B3),COLUMN(A2))))-1),1),"") Brief description: CBA() returns a negative number that can be used as an offset to pick up the value in a cell above the row you are currently examining. The IF statement says that if the cell on the same row in column A is 0, then go ahead and do the math, otherwise just display an empty string. The math grabs the value from column A in the same row and adds it to the value in Column A that is first one above it ahead of intervening blanks, and then divide that by the absolute value returned by CBA effectively decremented by 1 ( -4 + 1 = -3, ABS(-3) = 3) but if that number turns out to be zero (no intervening blank rows) then use 1 as the divisor rather than zero. You can then fill this formula down the page as far as you care to and it will do the math the way I believe you want it to do without ever having to re-enter formulas. Just delete/add entries in column A and the values in B are recalculated. The big thing to watch out for is the fact that the first value in B will be one you're not interested in, and will probably actually be invalid. Example, your first numeric entry in A is at row 4, then it is going to try to take the value in A4 and add it to some value that doesn't exist somewhere above it, which probably evaluates to zero and place a 4 into B4 and divide that by the number of blank cells above row 4, probably 3 of them. Wrong answer! After that things should work well. Problems could be caused by typing strange things into column A where the numbers are expected to be. It's rather ugly, someone may be able to improve upon it, but with the one major caveat, I believe it's very close to exactly what you are after. If you need assistance placing the UDF into a code module, instructions for using the VB Editor to do so can be found he http://www.jlathamsite.com/Teach/Excel_GP_Code.htm Just copy the code above and paste it into a code module in the workbook. "Tom Watt" wrote: The problem is specifying the cells each month. One month values may exist in C5 and C12, the next month they may be in C4 and C13. I would like to create one set of formulas, lock them and they work.. Thanks, Tom JLatham wrote: You should be able to specify a range with the COUNTBLANK function. in D12, try this formula: =(C12-C5)/COUNTBLANK(C5:C12) "Tom Watt" wrote: Hello, I have used the LOOKUP formula to find the value of the the next cell up with data (because there are blank lines seperating them), and this works. But now I need to divide the difference of two values by the number of blank lines that seperates them plus 1 (the number of days that the two values are apart). COUNTBLANK will not work because it will return the amount of blank cells total in a column, not just ones that are seperating two values (which are not always in the same cells). Example: This month there happens to be a value 101,010 in C5 and later a value 102,020 in C12. In D12 I would like the difference (102,020-101,010) divided by the number of cells apart (C12-C5=7). Thanks, Tom |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup/count blank cells problem
Thanks unfortunately a UDF isnt an option because this has to work in
Pocket Excel too. I never would have posted if so because I would have just done it in VBA already. :) In the other thread (linked in this one) I mentioned I got the suggested formulas to work. Does your long formula do the same thing as those? Thanks, Tom JLatham wrote: I couldn't find/figure out a worksheet function that does the coding equivalent of .End(xlUp).Row perhaps someone will provide one. If I had that, then I could have done this without a UDF (User Defined Function). But I created a UDF (macro function) and by using it, I have created this monstrosity that I think will do what you want with a couple of "watch out for's". First, the User Defined Function code: Function CBA(CellAddress As String) 'CBA = Counts Blanks Above 'cell referenced as the CellAddress Dim EndRow As Long Dim CurrentAddress As String CurrentAddress = CellAddress ' black box it EndRow = Range(CurrentAddress).End(xlUp).Row CBA = EndRow - Range(CurrentAddress).Row End Function Now for the really fun part - the formula to place into the cell where you want the calculations to be performed. Assuming a layout where Row 1 on the sheet contains column headers/titles like: A B ...other column labels 1 Quantity Average ....other column titles/headers 2 7 3 4 5 8 then in column B, Row 3 (1 row down from 1st real data entry in column A) enter this formula: =IF(OFFSET(B3,0,-1)0,(OFFSET(B3,0,-1)+ OFFSET(B3,(CBA(ADDRESS(ROW(B3),COLUMN(A2)))),-1))/IF((ABS(CBA(ADDRESS(ROW(B3),COLUMN(A2))))-1)0,(ABS(CBA(ADDRESS(ROW(B3),COLUMN(A2))))-1),1),"") Brief description: CBA() returns a negative number that can be used as an offset to pick up the value in a cell above the row you are currently examining. The IF statement says that if the cell on the same row in column A is 0, then go ahead and do the math, otherwise just display an empty string. The math grabs the value from column A in the same row and adds it to the value in Column A that is first one above it ahead of intervening blanks, and then divide that by the absolute value returned by CBA effectively decremented by 1 ( -4 + 1 = -3, ABS(-3) = 3) but if that number turns out to be zero (no intervening blank rows) then use 1 as the divisor rather than zero. You can then fill this formula down the page as far as you care to and it will do the math the way I believe you want it to do without ever having to re-enter formulas. Just delete/add entries in column A and the values in B are recalculated. The big thing to watch out for is the fact that the first value in B will be one you're not interested in, and will probably actually be invalid. Example, your first numeric entry in A is at row 4, then it is going to try to take the value in A4 and add it to some value that doesn't exist somewhere above it, which probably evaluates to zero and place a 4 into B4 and divide that by the number of blank cells above row 4, probably 3 of them. Wrong answer! After that things should work well. Problems could be caused by typing strange things into column A where the numbers are expected to be. It's rather ugly, someone may be able to improve upon it, but with the one major caveat, I believe it's very close to exactly what you are after. If you need assistance placing the UDF into a code module, instructions for using the VB Editor to do so can be found he http://www.jlathamsite.com/Teach/Excel_GP_Code.htm Just copy the code above and paste it into a code module in the workbook. "Tom Watt" wrote: The problem is specifying the cells each month. One month values may exist in C5 and C12, the next month they may be in C4 and C13. I would like to create one set of formulas, lock them and they work.. Thanks, Tom JLatham wrote: You should be able to specify a range with the COUNTBLANK function. in D12, try this formula: =(C12-C5)/COUNTBLANK(C5:C12) "Tom Watt" wrote: Hello, I have used the LOOKUP formula to find the value of the the next cell up with data (because there are blank lines seperating them), and this works. But now I need to divide the difference of two values by the number of blank lines that seperates them plus 1 (the number of days that the two values are apart). COUNTBLANK will not work because it will return the amount of blank cells total in a column, not just ones that are seperating two values (which are not always in the same cells). Example: This month there happens to be a value 101,010 in C5 and later a value 102,020 in C12. In D12 I would like the difference (102,020-101,010) divided by the number of cells apart (C12-C5=7). Thanks, Tom |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup/count blank cells problem
Well, I didn't think I could make it uglier, but I believe I've succeeded!! <g
This version of the formula will inhibit displaying that first invalid, probably unwanted entry where no numeric entries appear earlier in the column. It basically adds another check that says if sum of cell on same row plus sum of cell we're looking at earlier in the column is same as value of cell (in column A) of this row, then don't display anything. But this would also inhibit display of anything in situations where the earlier number in column A is zero. Everything seems to have its price. Again, for cell B3 in our examples (yes, its all one long formula) : =IF(OFFSET(B3,0,-1)+OFFSET(B3,(CBA(ADDRESS(ROW(B3),COLUMN(A2)))),-1)=OFFSET(B3,0,-1),"",IF(OFFSET(B3,0,-1)0,(OFFSET(B3,0,-1)+ OFFSET(B3,(CBA(ADDRESS(ROW(B3),COLUMN(A2)))),-1))/IF((ABS(CBA(ADDRESS(ROW(B3),COLUMN(A2))))-1)0,(ABS(CBA(ADDRESS(ROW(B3),COLUMN(A2))))-1),1),"")) "Tom Watt" wrote: The problem is specifying the cells each month. One month values may exist in C5 and C12, the next month they may be in C4 and C13. I would like to create one set of formulas, lock them and they work.. Thanks, Tom JLatham wrote: You should be able to specify a range with the COUNTBLANK function. in D12, try this formula: =(C12-C5)/COUNTBLANK(C5:C12) "Tom Watt" wrote: Hello, I have used the LOOKUP formula to find the value of the the next cell up with data (because there are blank lines seperating them), and this works. But now I need to divide the difference of two values by the number of blank lines that seperates them plus 1 (the number of days that the two values are apart). COUNTBLANK will not work because it will return the amount of blank cells total in a column, not just ones that are seperating two values (which are not always in the same cells). Example: This month there happens to be a value 101,010 in C5 and later a value 102,020 in C12. In D12 I would like the difference (102,020-101,010) divided by the number of cells apart (C12-C5=7). Thanks, Tom |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup/count blank cells problem
Take a look at your original post!
-- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Tom Watt" wrote in message ps.com... The problem is specifying the cells each month. One month values may exist in C5 and C12, the next month they may be in C4 and C13. I would like to create one set of formulas, lock them and they work.. Thanks, Tom JLatham wrote: You should be able to specify a range with the COUNTBLANK function. in D12, try this formula: =(C12-C5)/COUNTBLANK(C5:C12) "Tom Watt" wrote: Hello, I have used the LOOKUP formula to find the value of the the next cell up with data (because there are blank lines seperating them), and this works. But now I need to divide the difference of two values by the number of blank lines that seperates them plus 1 (the number of days that the two values are apart). COUNTBLANK will not work because it will return the amount of blank cells total in a column, not just ones that are seperating two values (which are not always in the same cells). Example: This month there happens to be a value 101,010 in C5 and later a value 102,020 in C12. In D12 I would like the difference (102,020-101,010) divided by the number of cells apart (C12-C5=7). Thanks, Tom |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup/count blank cells problem
"Take a look at your original post!"
Duh - maybe it's the fact that it's 3 a.m. right now, but I'm not picking up on the hint? While the simple formula I originally posted would handle a specific case, I thought the desire was to just have a "general" formula that would be left on the sheet down thru the column and then empty out the numbers in column A and put them back in each month. That's why I scratched my head real hard and came up with that somewhat nasty general formula? "RagDyer" wrote: Take a look at your original post! -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Tom Watt" wrote in message ps.com... The problem is specifying the cells each month. One month values may exist in C5 and C12, the next month they may be in C4 and C13. I would like to create one set of formulas, lock them and they work.. Thanks, Tom JLatham wrote: You should be able to specify a range with the COUNTBLANK function. in D12, try this formula: =(C12-C5)/COUNTBLANK(C5:C12) "Tom Watt" wrote: Hello, I have used the LOOKUP formula to find the value of the the next cell up with data (because there are blank lines seperating them), and this works. But now I need to divide the difference of two values by the number of blank lines that seperates them plus 1 (the number of days that the two values are apart). COUNTBLANK will not work because it will return the amount of blank cells total in a column, not just ones that are seperating two values (which are not always in the same cells). Example: This month there happens to be a value 101,010 in C5 and later a value 102,020 in C12. In D12 I would like the difference (102,020-101,010) divided by the number of cells apart (C12-C5=7). Thanks, Tom |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup/count blank cells problem
You'll notice that my message was addressed to, and sent as an answer to,
the OP! I wanted him to check on *his* original post on this subject. http://tinyurl.com/jtjg8 -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "JLatham" wrote in message ... "Take a look at your original post!" Duh - maybe it's the fact that it's 3 a.m. right now, but I'm not picking up on the hint? While the simple formula I originally posted would handle a specific case, I thought the desire was to just have a "general" formula that would be left on the sheet down thru the column and then empty out the numbers in column A and put them back in each month. That's why I scratched my head real hard and came up with that somewhat nasty general formula? "RagDyer" wrote: Take a look at your original post! -- Regards, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "Tom Watt" wrote in message ps.com... The problem is specifying the cells each month. One month values may exist in C5 and C12, the next month they may be in C4 and C13. I would like to create one set of formulas, lock them and they work.. Thanks, Tom JLatham wrote: You should be able to specify a range with the COUNTBLANK function. in D12, try this formula: =(C12-C5)/COUNTBLANK(C5:C12) "Tom Watt" wrote: Hello, I have used the LOOKUP formula to find the value of the the next cell up with data (because there are blank lines seperating them), and this works. But now I need to divide the difference of two values by the number of blank lines that seperates them plus 1 (the number of days that the two values are apart). COUNTBLANK will not work because it will return the amount of blank cells total in a column, not just ones that are seperating two values (which are not always in the same cells). Example: This month there happens to be a value 101,010 in C5 and later a value 102,020 in C12. In D12 I would like the difference (102,020-101,010) divided by the number of cells apart (C12-C5=7). Thanks, Tom |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count blank cells containing function | Excel Discussion (Misc queries) | |||
Counting blank cells until value is reached | Excel Worksheet Functions | |||
Blank Rows from Merged Cells in Drop Down Menu | Excel Discussion (Misc queries) | |||
blank cells | Excel Discussion (Misc queries) | |||
Blank Cells in Pivot Tables | Excel Discussion (Misc queries) |