software

i have formulas in a range L5:L15 which sometimes return some value and

sometimes zero. i want to give them auto numbers in column M in a way
that it
should only count the cell which has some value.
suppose formula in L5 returns some value, L6 also then L7 amp; L8 have no

value(but formula persists), cell L9, L10, L11 has values then L12 has
no
value L13, L14 has value and L15 has no value (but it has formula in
it)
values in these cells changes and some goes to zero and some return
values.
now i want to give them Auto Numbers in a way that cells with some
value
should only be considered.

is there any function to solve this problem.
regards--
starguy
------------------------------------------------------------------------
starguy's Profile: www.excelforum.com/member.php...oamp;userid=32434
View this thread: www.excelforum.com/showthread...hreadid=526263Insert the wollowing code in your sheet's code (right-click on tab, select
Code)

'--------------------------
Private Sub Worksheet_Calculate()
Const strRngCheck = quot;L5:L15quot;
Dim rng As Range
Dim iNum As Long

iNum = 0
For Each rng In Range(strRngCheck)
If rng.Value = 0 Then
rng.Offset(0, 1).Value = quot;quot;
Else
iNum = iNum 1
rng.Offset(0, 1).Value = iNum
End If
Next rng

End Sub
'----------------------------

quot;starguyquot; gt; a écrit
dans le message de
...
gt;
gt; i have formulas in a range L5:L15 which sometimes return some value and
gt;
gt; sometimes zero. i want to give them auto numbers in column M in a way
gt; that it
gt; should only count the cell which has some value.
gt; suppose formula in L5 returns some value, L6 also then L7 amp; L8 have no
gt;
gt; value(but formula persists), cell L9, L10, L11 has values then L12 has
gt; no
gt; value L13, L14 has value and L15 has no value (but it has formula in
gt; it)
gt; values in these cells changes and some goes to zero and some return
gt; values.
gt; now i want to give them Auto Numbers in a way that cells with some
gt; value
gt; should only be considered.
gt;
gt; is there any function to solve this problem.
gt; regards
gt;
gt;
gt; --
gt; starguy
gt; ------------------------------------------------------------------------
gt; starguy's Profile:
www.excelforum.com/member.php...oamp;userid=32434
gt; View this thread: www.excelforum.com/showthread...hreadid=526263
gt;

thank you Ardus but i want this by using a function because i dont know
coding
thank you if you could tell me any function to do this.--
starguy
------------------------------------------------------------------------
starguy's Profile: www.excelforum.com/member.php...oamp;userid=32434
View this thread: www.excelforum.com/showthread...hreadid=526263I don't think you can do this with a function

--
AP

quot;starguyquot; gt; a écrit dans
le message de ...
gt;
gt; thank you Ardus but i want this by using a function because i dont know
gt; coding
gt; thank you if you could tell me any function to do this.
gt;
gt;
gt; --
gt; starguy
gt; ------------------------------------------------------------------------
gt; starguy's Profile:
www.excelforum.com/member.php...oamp;userid=32434
gt; View this thread: www.excelforum.com/showthread...hreadid=526263
gt;

Hi Starguy,

Try entering this forrmula into cell M5 amp; copying down:
=IF(L5gt;0,MAX($M$4:M4) 1,0)

note: the dollar sign is only included in the first part of the max
function - this locks the starting point of the range to check at M4
(if M5 was used a circular reference would be created).

The above formula will enter a zero in any row of col M where col L has
no value, to make it appear that col M is empty on these rows use:
=IF(L5gt;0,MAX($M$4:M4) 1,quot;quot;)

hth
Rob Brockett
NZ
Always learning amp; the best way to learn is to experience...--
broro183
------------------------------------------------------------------------
broro183's Profile: www.excelforum.com/member.php...oamp;userid=30068
View this thread: www.excelforum.com/showthread...hreadid=526263Your motto came true!

--
AP

quot;broro183quot; gt; a écrit
dans le message de
...
gt;
gt; Hi Starguy,
gt;
gt; Try entering this forrmula into cell M5 amp; copying down:
gt; =IF(L5gt;0,MAX($M$4:M4) 1,0)
gt;
gt; note: the dollar sign is only included in the first part of the max
gt; function - this locks the starting point of the range to check at M4
gt; (if M5 was used a circular reference would be created).
gt;
gt; The above formula will enter a zero in any row of col M where col L has
gt; no value, to make it appear that col M is empty on these rows use:
gt; =IF(L5gt;0,MAX($M$4:M4) 1,quot;quot;)
gt;
gt; hth
gt; Rob Brockett
gt; NZ
gt; Always learning amp; the best way to learn is to experience...
gt;
gt;
gt; --
gt; broro183
gt; ------------------------------------------------------------------------
gt; broro183's Profile:
www.excelforum.com/member.php...oamp;userid=30068
gt; View this thread: www.excelforum.com/showthread...hreadid=526263
gt;

Beauty :-)
Pleased I could help.

I use it for numbering instruction sheets, as I find it's quicker to
correct than having to reautofill etc after deciding another step needs
to be added in for end users' clarity.

Rob Brockett
NZ
Always learning amp; the best way to learn is to experience...Ardus Petus Wrote:
gt; Your motto came true!
gt;
gt; --
gt; AP
gt;
gt; quot;broro183quot; gt; a
gt; écrit
gt; dans le message de
gt; ...
gt; gt;
gt; gt; Hi Starguy,
gt; gt;
gt; gt; Try entering this forrmula into cell M5 amp; copying down:
gt; gt; =IF(L5gt;0,MAX($M$4:M4) 1,0)
gt; gt;
gt; gt; note: the dollar sign is only included in the first part of the max
gt; gt; function - this locks the starting point of the range to check at M4
gt; gt; (if M5 was used a circular reference would be created).
gt; gt;
gt; gt; The above formula will enter a zero in any row of col M where col L
gt; has
gt; gt; no value, to make it appear that col M is empty on these rows use:
gt; gt; =IF(L5gt;0,MAX($M$4:M4) 1,quot;quot;)
gt; gt;
gt; gt; hth
gt; gt; Rob Brockett
gt; gt; NZ
gt; gt; Always learning amp; the best way to learn is to experience...
gt; gt;
gt; gt;
gt; gt; --
gt; gt; broro183
gt; gt;
gt; ------------------------------------------------------------------------
gt; gt; broro183's Profile:
gt; www.excelforum.com/member.php...oamp;userid=30068
gt; gt; View this thread:
gt; www.excelforum.com/showthread...hreadid=526263
gt; gt;--
broro183
------------------------------------------------------------------------
broro183's Profile: www.excelforum.com/member.php...oamp;userid=30068
View this thread: www.excelforum.com/showthread...hreadid=526263Hi Starguy,OK, well, it really depends what you want to do and from the information
below it doesn't appear to clear. So I will give you a few options. If you
don't want to count the zero's but count the number of cells with data above
zero in it, you can use the following formula:

=countif(L5:L15,quot;gt;0quot;)

if you are wanting to calculate the results in a manner that you don't want
the zero's to affect the total (say for example, mean calculations, medium)
then you could employ an if statement as follows:

=if(lt;your_formulagt;=0,quot;quot;,lt;your_formulagt;)

N.B. lt;your_formulagt; is the existing formula in the cell range L5:L15.

Auto Numbering, you could use an if statement in combination with the above
as follows:

in row J5:J15 have the following:

for cell J5 =gt; =countif(L5,quot;gt;0quot;)

then in row K5:K15 have the following:

=if(lt;your_formulagt;=0,quot;quot;,sum(J5:lt;current J cellgt;)

For more intuitive examples, it might be good to post examples of the
formulae that you are using in the cell range, data isn't necessary, just the
formula.

Hope that helps.Franksta.

thank you Ardus but i want this by using a function because i dont know coding
thank you if you could tell me any function to do this.

quot;Ardus Petusquot; wrote:

gt; Insert the wollowing code in your sheet's code (right-click on tab, select
gt; Code)
gt;
gt; '--------------------------
gt; Private Sub Worksheet_Calculate()
gt; Const strRngCheck = quot;L5:L15quot;
gt; Dim rng As Range
gt; Dim iNum As Long
gt;
gt; iNum = 0
gt; For Each rng In Range(strRngCheck)
gt; If rng.Value = 0 Then
gt; rng.Offset(0, 1).Value = quot;quot;
gt; Else
gt; iNum = iNum 1
gt; rng.Offset(0, 1).Value = iNum
gt; End If
gt; Next rng
gt;
gt; End Sub
gt; '----------------------------
gt;
gt; quot;starguyquot; gt; a écrit
gt; dans le message de
gt; ...
gt; gt;
gt; gt; i have formulas in a range L5:L15 which sometimes return some value and
gt; gt;
gt; gt; sometimes zero. i want to give them auto numbers in column M in a way
gt; gt; that it
gt; gt; should only count the cell which has some value.
gt; gt; suppose formula in L5 returns some value, L6 also then L7 amp; L8 have no
gt; gt;
gt; gt; value(but formula persists), cell L9, L10, L11 has values then L12 has
gt; gt; no
gt; gt; value L13, L14 has value and L15 has no value (but it has formula in
gt; gt; it)
gt; gt; values in these cells changes and some goes to zero and some return
gt; gt; values.
gt; gt; now i want to give them Auto Numbers in a way that cells with some
gt; gt; value
gt; gt; should only be considered.
gt; gt;
gt; gt; is there any function to solve this problem.
gt; gt; regards
gt; gt;
gt; gt;
gt; gt; --
gt; gt; starguy
gt; gt; ------------------------------------------------------------------------
gt; gt; starguy's Profile:
gt; www.excelforum.com/member.php...oamp;userid=32434
gt; gt; View this thread: www.excelforum.com/showthread...hreadid=526263
gt; gt;
gt;
gt;
gt;

have a suggestion, you can check your previous post for this.Thanks,

Franksta.