Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Countif
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, P1:P387, 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Countif
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 "Martin" wrote in message ... 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, P1:P387, 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Countif
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, P1:P387, 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Countif
Use this formula but be sure to array enter using control+shift+enter when
editing =AVERAGE(IF(L1:L90,L1:L9)) "Martin" wrote in message ... 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, P1:P387, 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Countif
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:L90,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 ... Use this formula but be sure to array enter using control+shift+enter when editing =AVERAGE(IF(L1:L90,L1:L9)) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I use a countif function according to two other countif fu. | Excel Worksheet Functions | |||
edit this =COUNTIF(A1:F16,"*1-2*")+COUNTIF(A1:F16,"*2-1*") | Excel Discussion (Misc queries) | |||
COUNTIF or not to COUNTIF on a range in another sheet | Excel Worksheet Functions | |||
countif | Excel Worksheet Functions | |||
COUNTIF in one colum then COUNTIF in another...??? | Excel Worksheet Functions |