Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default How to increase a variable after checking of cell is filled up or not?


I wanted to type a function whereby it will check if certain range of
cells are being filled in by letters/numbers. Example: If A1:C3 is not
blank, then the number of cells that are not blank will be reflected at
A4. Can I do this with excel?


--
wuming79
------------------------------------------------------------------------
wuming79's Profile: http://www.excelforum.com/member.php...o&userid=36037
View this thread: http://www.excelforum.com/showthread...hreadid=558301

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
Posts: 9,221
Default How to increase a variable after checking of cell is filled up or

"wuming79" wrote:
I wanted to type a function whereby it will check if certain range of
cells are being filled in by letters/numbers. Example: If A1:C3 is not
blank, then the number of cells that are not blank will be reflected at
A4. Can I do this with excel?


One way ..

Try either of the 2 formulas below in A4:
=SUMPRODUCT(--(A1:C3<""))
=SUMPRODUCT(--NOT(ISBLANK(A1:C3)))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default How to increase a variable after checking of cell is filled up or not?


Max would you please explain the working of -- in SUMPRODUCT formula. I
have read the article at xldynamic but could not understand the --
functionality.

Max Wrote:

=SUMPRODUCT(--(A1:C3<""))
=SUMPRODUCT(--NOT(ISBLANK(A1:C3)))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---



--
starguy
------------------------------------------------------------------------
starguy's Profile: http://www.excelforum.com/member.php...o&userid=32434
View this thread: http://www.excelforum.com/showthread...hreadid=558301

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default How to increase a variable after checking of cell is filled up or not?


yah, I was abt to ask the same question...excel's own help file is
rather useless...


--
wuming79
------------------------------------------------------------------------
wuming79's Profile: http://www.excelforum.com/member.php...o&userid=36037
View this thread: http://www.excelforum.com/showthread...hreadid=558301

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
Posts: 9,221
Default How to increase a variable after checking of cell is filled up

"starguy" wrote:
Max would you please explain the working of -- in SUMPRODUCT formula. I
have read the article at xldynamic but could not understand the --
functionality.


The "--" is what they call a double unary which coerces the TRUE / FALSE
returns in the comparisons evaluated within the parens to numeric 1's / 0's

Example:
=SUMPRODUCT(--(A1:C3<""))


Supposing we have inputs made within A1:C3 , say specifically in A2, B3 and
C1 (with all other cells within A1:C3 either "blank"* or empty)
*eg: there may be formulas within A1:C3 returning zero length null strings: ""

Then in A4: =SUMPRODUCT(--(A1:C3<""))

would resolve initially to:
=SUMPRODUCT(--({FALSE,FALSE,TRUE;TRUE,FALSE,FALSE;FALSE,TRUE,FAL SE}))

We can see the above happen by carefully selecting the innermost part of the
formula, viz. just the part: A1:C3<""
in the formula bar, then press F9 key

and then resolve to:
=SUMPRODUCT({0,0,1;1,0,0;0,1,0})
[ To see the above, just select the 2nd layer part viz.: --(A1:C3<"")
then press F9]

The "--" will coerce FALSEs to 0's, TRUEs to 1's
The 0's and 1's finally gets evaluated by the SUMPRODUCT
and returns in A4: 3

Try also JE McGimpsey's page for a good explanation at:
http://www.mcgimpsey.com/excel/formulae/doubleneg.html
Why use -- in SUMPRODUCT formulae

And .. Jason Morin explains it in depth in this past post
(double unary technique popularized by Harlan Grove):
http://tinyurl.com/fnt7v

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---


  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
Posts: 9,221
Default How to increase a variable after checking of cell is filled up

"wuming79" wrote:
yah, I was abt to ask the same question...excel's own help file is
rather useless...


Pl see my response to starguy ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default How to increase a variable after checking of cell is filled up or not?


Thanks for the quick response and solution :-)


--
wuming79
------------------------------------------------------------------------
wuming79's Profile: http://www.excelforum.com/member.php...o&userid=36037
View this thread: http://www.excelforum.com/showthread...hreadid=558301

  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
Posts: 9,221
Default How to increase a variable after checking of cell is filled up

"wuming79" wrote:
Thanks for the quick response and solution :-)


You're welcome !
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to have a variable cell reference across sheets? Shane Gibson Excel Discussion (Misc queries) 3 March 8th 06 11:10 PM
Urgent date/scheduling calc needed jct Excel Worksheet Functions 3 February 24th 06 02:36 AM
Can the column index in a cell address be made variable? cyberdude Excel Discussion (Misc queries) 1 November 20th 05 03:47 AM
Linking a cell to another workbook cell based on a variable name Brian Excel Discussion (Misc queries) 6 June 1st 05 11:54 PM
Checking if a cell is filled up with data xpucto Excel Discussion (Misc queries) 1 May 26th 05 10:17 AM


All times are GMT +1. The time now is 02:30 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"