Counting blank cells
Hi again, I've got a list like this: 45 32 23 blank 34 45 blank blank blank any idea how to count the number of blanks after the final non-blank cell? -- geoff1234 ------------------------------------------------------------------------ geoff1234's Profile: http://www.excelforum.com/member.php...o&userid=36051 View this thread: http://www.excelforum.com/showthread...hreadid=559397 |
Counting blank cells
The formula below assumes the values are in Column A, rows 1 through 27, you
will need to adjust the range as necessary: =COUNTIF(A1:A27,"") The criteria following the comma in the formula is two quotation marks w/o a space, not four consecutive apostrophes. -- Kevin Backmann "geoff1234" wrote: Hi again, I've got a list like this: 45 32 23 blank 34 45 blank blank blank any idea how to count the number of blanks after the final non-blank cell? -- geoff1234 ------------------------------------------------------------------------ geoff1234's Profile: http://www.excelforum.com/member.php...o&userid=36051 View this thread: http://www.excelforum.com/showthread...hreadid=559397 |
Counting blank cells
Try...
=COUNTBLANK(INDEX(A2:A10,MATCH(BigNum,A2:A10)+1):A 10) ....where BigNum is defined as 9.99999999999999E+307. Hope this helps! In article , geoff1234 wrote: Hi again, I've got a list like this: 45 32 23 blank 34 45 blank blank blank any idea how to count the number of blanks after the final non-blank cell? |
Counting blank cells
Hey Domenic,
What's your take on 99^99 ? Any opinions or foreseeable problems? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Domenic" wrote in message ... Try... =COUNTBLANK(INDEX(A2:A10,MATCH(BigNum,A2:A10)+1):A 10) ...where BigNum is defined as 9.99999999999999E+307. Hope this helps! In article , geoff1234 wrote: Hi again, I've got a list like this: 45 32 23 blank 34 45 blank blank blank any idea how to count the number of blanks after the final non-blank cell? |
Counting blank cells
Hi RagDyer!
As far as I can tell, I don't think it would be a problem in most cases. But personally, I prefer to use 9.99999999999999E+307. Since it's the largest number Excel recognizes, its use eliminates any risk whatsoever regardless of how small or remote. Cheers! In article , "Ragdyer" wrote: Hey Domenic, What's your take on 99^99 ? Any opinions or foreseeable problems? -- Regards, RD |
Counting blank cells
But the 99^99 performs exactly the same function (no larger XL number), and
is easier to type.<bg Been using it for a while in some formulas at my plant, and am hoping that I don't get bitten with any unforeseen future problems. -- Regards, RD ---------------------------------------------------------------------------- ------------------- Please keep all correspondence within the Group, so all may benefit ! ---------------------------------------------------------------------------- ------------------- "Domenic" wrote in message ... Hi RagDyer! As far as I can tell, I don't think it would be a problem in most cases. But personally, I prefer to use 9.99999999999999E+307. Since it's the largest number Excel recognizes, its use eliminates any risk whatsoever regardless of how small or remote. Cheers! In article , "Ragdyer" wrote: Hey Domenic, What's your take on 99^99 ? Any opinions or foreseeable problems? -- Regards, RD |
Counting blank cells
In article ,
"RagDyeR" wrote: But the 99^99 performs exactly the same function (no larger XL number), and is easier to type.<bg Sure! In practice, it's not likely that data will contain a number larger than 99^99. However, since it's theoretically possible... <bg Cheers! |
Counting blank cells
thanks for all the help, got it working nicely now this forum is awesome peace out -- geoff1234 ------------------------------------------------------------------------ geoff1234's Profile: http://www.excelforum.com/member.php...o&userid=36051 View this thread: http://www.excelforum.com/showthread...hreadid=559397 |
All times are GMT +1. The time now is 01:51 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com