Countif

[Martin]

Most of my programming experience has come from the VBE and the various
newsgroups, now I'm stuck.

I have a sheet with info in cells from a1 to n387. Daily, another 387 items
will be added, ie O1:O387, P1387, etc. I need to determine the number of
items on each row that are equal to or greater than 0 and then take this
number and divide by the total number of items in the row which would then
give me the %ge of results that are not negative. My idea was that in d4 and
c4 I could put a formula which could then be filled down to work for the
rest of the rows.

I managed [d4]

[d4].Formula = "=Counta(" & [l4].Address & ":" & [iv4].End(xlToLeft).Address
& ")"

but I'm stuck on [c4] where I'm trying for something on the lines of:

[c4].Formula = "=Countif([l4]:[iv4].End(xlToLeft))=>0"

but, obviously, this doesn't work. Having searched Google and tried various
combinations of this and others (Application, WorksheetFunction), I'm still
no further on. Can somebody please help.

Thanks
Martin

 

[Robin Hammond]

I'm not entirely clear why you don't just write the formulas in and copy
across a column each day when data is added.

It also looks like there is a flaw in your formula. Countif reads like this
with quotes around the >0 bit:

=COUNTIF(A1:A4,">=0")/COUNT(A1:A4)

Robin Hammond
www.enhanceddatasystems.com
Check out our XspandXL add-in

 

[Pete McCosh]

Martin,

I was a bit confused by the references in you rpost, but I
have assumed the actual data you want to analyse begins in
column I, even though it starts at A1?
A
lso, is there any particualr reason why this has to be
done in VBA? It would seem to me to be a lot easier to
type these formulae in the cells then copy them down:
C4 =COUNTA(I4:IV4)
C5 =COUNTIF(I4:IV4,">=0")

If the formulae have to be put into each new days' row
after it has been imported, it would still be easier to
include code to copy and paste them from the previous row.

Pete

I have a sheet with info in cells from a1 to n387. Daily,

another 387 items will be added, ie O1:O387, P1387, etc.
I need to determine the number of items on each row that
are equal to or greater than 0 and then take this number
and divide by the total number of items in the row which
would then give me the %ge of results that are not
negative.

 

[Don Guillett]

Use this formula but be sure to array enter using control+shift+enter when
editing
=AVERAGE(IF(L1:L9>0,L1:L9))

 

[Martin]

Pete, Robin and Don,

Thanks for the quick response, having looked back at this, I take the
comment about the flaw in the formula. I realise also that my "COUNTA" is
incorrect as it has absolute references on the worksheet.

I am doing this in code because:
1. As new sets of figures are introduced, space is made for them by
inserting a column at K1. All other columns move to the right and, of
course, the references move with them.
2. Because of the number of different formulae in this workbook, I am at the
same time writing a "Backup", which is stored separately, which could be
used for reconstruction if required. (I have had a hard disk fail on me in
the past)

I said I was stuck - I was obviously also confused - Sorry!

What I am trying to do is, after new figures are inserted and everything has
been moved, take the first value, which will always be in L4 to the last
value (the equivalent of IV4.End(xlToLeft), do COUNTA for this range, and
also COUNTIF for all items in this range which are either positive or zero
and thus get a %ge figure of all non-negative results.

Don,

=AVERAGE(IF(L1:L9>0,L1:L9)) gives me the average value of the contents of
the cells rather than the number of positive values as a percentage of the
total number of values.

Martin

Don Guillett wrote in message ...