Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Countif Problem
I'm having a problem with a calculation, it works fine if i know what the
last cell reference is, but unforunatley this figure will vary. How can i find out what the last cell reference is going to be ? and how do i use that in the calculation. Do i need to use a dummy reference ? The calculation i'm using is :- (countid(h4:h38,"<15"))/(countif(h4:h39,"0"))*100. 'H39' is the cell reference that will change depending on the amount of data on the spreadsheet |
#2
|
|||
|
|||
How about just using the whole column
=COUNTIF(H:H,"<15")/(COUNTIF(H:H,"0"))*100 -- HTH RP (remove nothere from the email address if mailing direct) "dave" wrote in message ... I'm having a problem with a calculation, it works fine if i know what the last cell reference is, but unforunatley this figure will vary. How can i find out what the last cell reference is going to be ? and how do i use that in the calculation. Do i need to use a dummy reference ? The calculation i'm using is :- (countid(h4:h38,"<15"))/(countif(h4:h39,"0"))*100. 'H39' is the cell reference that will change depending on the amount of data on the spreadsheet |
#3
|
|||
|
|||
You can always include blank cells without affecting the result, so go way
beyond where you need, like the entire column. =COUNTIF(H4:H65536,"<15")/COUNTIF(H4:H65536,"0")*100 "dave" wrote in message ... I'm having a problem with a calculation, it works fine if i know what the last cell reference is, but unforunatley this figure will vary. How can i find out what the last cell reference is going to be ? and how do i use that in the calculation. Do i need to use a dummy reference ? The calculation i'm using is :- (countid(h4:h38,"<15"))/(countif(h4:h39,"0"))*100. 'H39' is the cell reference that will change depending on the amount of data on the spreadsheet |
#4
|
|||
|
|||
If H39 stands for the last cell containing data, you can simply use
(countid(h4:h38,"<15"))/(countif(h4:h2000,"0"))*100 or some other suitably large row number that will not be exceeded. Otherwise, you need to say how the last row you want in the formula is to be recognized. Jerry dave wrote: I'm having a problem with a calculation, it works fine if i know what the last cell reference is, but unforunatley this figure will vary. How can i find out what the last cell reference is going to be ? and how do i use that in the calculation. Do i need to use a dummy reference ? The calculation i'm using is :- (countid(h4:h38,"<15"))/(countif(h4:h39,"0"))*100. 'H39' is the cell reference that will change depending on the amount of data on the spreadsheet |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
COUNTIF and automatic type conversion problem | Excel Discussion (Misc queries) | |||
COUNTIF and automatic type conversion problem | Excel Worksheet Functions | |||
countif problem | Excel Discussion (Misc queries) | |||
problem with countif | Excel Worksheet Functions | |||
Countif Problem | Excel Worksheet Functions |