Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
referencing non-blank cells
Hi all,
Say I have a column with cell values 4, 0, 0, 5, 0 , 0, 0, 2, 0, 3 What I am trying to do is have in the next column a formula which returns the number adjacent to it multiplied by the number of blanks between it and the next non-blank cell. So, for the example above, the output would be 8, 0, 0, 15, 0, 0, 0, 2 etc Thanks in advance, MM |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
referencing non-blank cells
Assuming the blank cells do not contain zeros,
(but may contain a formula that results in blanks), and the input column starts at A1 and is named set1 with Insert Name Define set1 Refers To: =A1:A100 seq1 Refers To: =ROW(INDEX(A:A,1):INDEX(A:A,ROWS(set1))) Into B1 enter this array formula with Cntrl+Shift+Enter =IF(set1 1:1="","",(MATCH(1,--((set1<"")*seq1ROW()),0)-ROW()-1) *set1 1:1) and copy down. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
referencing non-blank cells
Thanks very much, Herbert, I'll give that a shot.
Cheers, MM "Herbert Seidenberg" wrote: Assuming the blank cells do not contain zeros, (but may contain a formula that results in blanks), and the input column starts at A1 and is named set1 with Insert Name Define set1 Refers To: =A1:A100 seq1 Refers To: =ROW(INDEX(A:A,1):INDEX(A:A,ROWS(set1))) Into B1 enter this array formula with Cntrl+Shift+Enter =IF(set1 1:1="","",(MATCH(1,--((set1<"")*seq1ROW()),0)-ROW()-1) *set1 1:1) and copy down. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
referencing non-blank cells
It doesn't quite work, I'm afraid.
What I have (in row I) is : 12 3 spaces 11 7 spaces 11 7 spaces 12 3 spaces Your formula gives the correct value (36) for the first occupied row but then gives 33 instead of 77 for the second and #N/A for the last two. My guess is that this is due to MATCH requiring the array to be sorted (which it can't be), but I've never used arrays before, so I'm only guessing. I'll keep plugging away - at the very least you've opened a new avenue of exploration for me! Cheers, MM "Herbert Seidenberg" wrote: Assuming the blank cells do not contain zeros, (but may contain a formula that results in blanks), and the input column starts at A1 and is named set1 with Insert Name Define set1 Refers To: =A1:A100 seq1 Refers To: =ROW(INDEX(A:A,1):INDEX(A:A,ROWS(set1))) Into B1 enter this array formula with Cntrl+Shift+Enter =IF(set1 1:1="","",(MATCH(1,--((set1<"")*seq1ROW()),0)-ROW()-1) *set1 1:1) and copy down. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
referencing non-blank cells
The formula will not work if the data is arranged in a row.
It has to be in a column, starting at row 1 (one). To make it work at any other starting row, you have to bring seq into a displayed column and refer to seq instead of row(). Seq is a simple counting sequence 1,2,3....n as shown: 1 12 36 2 3 4 5 11 77 6 7 8 9 10 11 12 13 11 77 14 15 16 17 18 19 20 21 12 36 22 23 24 25 999 #N/A The last number (999) will give a #N/A since it only serves to define how many spaces are below the number 12. If your data is arranged in a horizontal row, you can make it work by substituting row refs with column refs. Using R1C1 ref style might help. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
referencing non-blank cells
Hi!
Try this: Assume your data: What I have (in row I) is : 12 3 spaces 11 7 spaces 11 7 spaces 12 3 spaces Starts in cell A1. Based on the above that would make the range A1:A24. This formula works but it needs a value in the last cell of the range. Since your data range ends in A24 you could put (end) in cell A25. This is a fairly common practice where ranges have empty cells at their end. So, now you would have this data: 12 3 spaces 11 7 spaces 11 7 spaces 12 3 spaces (End) Then enter this formula in B1 as an array using the key combination of CTRL,SHIFT,ENTER: =IF(A1<"",(MATCH(TRUE,A2:A$250,0)-1)*A1,"") Copy down to A24. A25 is not considered part of the data range, it's just a "marker". Biff "MM" wrote in message ... It doesn't quite work, I'm afraid. What I have (in row I) is : 12 3 spaces 11 7 spaces 11 7 spaces 12 3 spaces Your formula gives the correct value (36) for the first occupied row but then gives 33 instead of 77 for the second and #N/A for the last two. My guess is that this is due to MATCH requiring the array to be sorted (which it can't be), but I've never used arrays before, so I'm only guessing. I'll keep plugging away - at the very least you've opened a new avenue of exploration for me! Cheers, MM "Herbert Seidenberg" wrote: Assuming the blank cells do not contain zeros, (but may contain a formula that results in blanks), and the input column starts at A1 and is named set1 with Insert Name Define set1 Refers To: =A1:A100 seq1 Refers To: =ROW(INDEX(A:A,1):INDEX(A:A,ROWS(set1))) Into B1 enter this array formula with Cntrl+Shift+Enter =IF(set1 1:1="","",(MATCH(1,--((set1<"")*seq1ROW()),0)-ROW()-1) *set1 1:1) and copy down. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
referencing non-blank cells
Herbert,
Sorry, my bad - the data is in a column not a row. Biff, your formula just returns zeros against the non-blanks! Thanks for the input, MM |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
referencing non-blank cells
Biff's formula works for me! Regards Dav -- Dav ------------------------------------------------------------------------ Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107 View this thread: http://www.excelforum.com/showthread...hreadid=544223 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
referencing non-blank cells
You're right!
I just re-tried it in a new sheet with made up numbers and it worked. Must be something to do with how my original sheet is set up. Back to the drawing board... "Dav" wrote: Biff's formula works for me! Regards Dav -- Dav ------------------------------------------------------------------------ Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107 View this thread: http://www.excelforum.com/showthread...hreadid=544223 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
referencing non-blank cells
Ok, I've had a chance to look into this some more.
If I put Biff's formula into a column next to a column of made up numbers and spaces, it works. When I put it into my sheet it returns 0. Presumably, this is because the (MATCH)-1 part evaluates to zero. The formula in the cell to the left (in column I) is: =IF(A3="End of job", "end",IF(ISERROR(IF(ISBLANK(C2)=FALSE,C2+H2,""))," ",IF(ISBLANK(C2)=FALSE,C2+H2,""))) I tried inserting another column with =I1 etc in it - same result. Hope this helps, MM |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
referencing non-blank cells
The formula in the cell to the left (in column I) is:
=IF(A3="End of job", "end",IF(ISERROR(IF(ISBLANK(C2)=FALSE,C2+H2,"")), "",IF(ISBLANK(C2)=FALSE,C2+H2,""))) OK, that means the cells have formula blanks in them, so, try this: Still array entered: =IF(ISNUMBER(A1),(MATCH(TRUE,A2:A$25<"",0)-1)*A1,"") Biff "MM" wrote in message ... Ok, I've had a chance to look into this some more. If I put Biff's formula into a column next to a column of made up numbers and spaces, it works. When I put it into my sheet it returns 0. Presumably, this is because the (MATCH)-1 part evaluates to zero. The formula in the cell to the left (in column I) is: =IF(A3="End of job", "end",IF(ISERROR(IF(ISBLANK(C2)=FALSE,C2+H2,""))," ",IF(ISBLANK(C2)=FALSE,C2+H2,""))) I tried inserting another column with =I1 etc in it - same result. Hope this helps, MM |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
referencing non-blank cells
Yep, that does the trick! I was going to try something similar this morning
as it happens. Thank you all for your help. MM "Biff" wrote: The formula in the cell to the left (in column I) is: =IF(A3="End of job", "end",IF(ISERROR(IF(ISBLANK(C2)=FALSE,C2+H2,"")), "",IF(ISBLANK(C2)=FALSE,C2+H2,""))) OK, that means the cells have formula blanks in them, so, try this: Still array entered: =IF(ISNUMBER(A1),(MATCH(TRUE,A2:A$25<"",0)-1)*A1,"") Biff "MM" wrote in message ... Ok, I've had a chance to look into this some more. If I put Biff's formula into a column next to a column of made up numbers and spaces, it works. When I put it into my sheet it returns 0. Presumably, this is because the (MATCH)-1 part evaluates to zero. The formula in the cell to the left (in column I) is: =IF(A3="End of job", "end",IF(ISERROR(IF(ISBLANK(C2)=FALSE,C2+H2,""))," ",IF(ISBLANK(C2)=FALSE,C2+H2,""))) I tried inserting another column with =I1 etc in it - same result. Hope this helps, MM |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
referencing non-blank cells
You're welcome!
Biff "MM" wrote in message ... Yep, that does the trick! I was going to try something similar this morning as it happens. Thank you all for your help. MM "Biff" wrote: The formula in the cell to the left (in column I) is: =IF(A3="End of job", "end",IF(ISERROR(IF(ISBLANK(C2)=FALSE,C2+H2,"")), "",IF(ISBLANK(C2)=FALSE,C2+H2,""))) OK, that means the cells have formula blanks in them, so, try this: Still array entered: =IF(ISNUMBER(A1),(MATCH(TRUE,A2:A$25<"",0)-1)*A1,"") Biff "MM" wrote in message ... Ok, I've had a chance to look into this some more. If I put Biff's formula into a column next to a column of made up numbers and spaces, it works. When I put it into my sheet it returns 0. Presumably, this is because the (MATCH)-1 part evaluates to zero. The formula in the cell to the left (in column I) is: =IF(A3="End of job", "end",IF(ISERROR(IF(ISBLANK(C2)=FALSE,C2+H2,""))," ",IF(ISBLANK(C2)=FALSE,C2+H2,""))) I tried inserting another column with =I1 etc in it - same result. Hope this helps, MM |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel inserted 0's in cells linked to blank cells | Excel Discussion (Misc queries) | |||
CONCATENATE problem with blank cells | Excel Discussion (Misc queries) | |||
Copy down - special to fill only the blank cells | Excel Discussion (Misc queries) | |||
referencing cells that return blank results | Excel Worksheet Functions | |||
Non Blank - Blank Cells???? | Excel Discussion (Misc queries) |