Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Calculation problem?
I have a spreadsheet that uses the following calculation:-
'=(COUNTIF(Calc!H2:H85,"<15"))/(COUNTIF(Calc!H2:H85,"0"))*100' The problem i have is that while H2 is constant, H85 can change depending on how many rows their are? I am trying to use a terminating value such a 'end' so it knows that when i reach 'end' it uses the previous row reference to replace what is currently 'H85'. Is it possible? Hope this makes sense. |
#2
|
|||
|
|||
Unfortunatley i've tried that, it distorts the figures as it tries to
calculate the % for the whole table not just to were the data ends. "Bob Phillips" wrote: Why not just use =(COUNTIF(Calc!H2:H65536,"<15"))/(COUNTIF(Calc!H2:H65536,"0"))*100 -- HTH RP (remove nothere from the email address if mailing direct) "dave" wrote in message ... I have a spreadsheet that uses the following calculation:- '=(COUNTIF(Calc!H2:H85,"<15"))/(COUNTIF(Calc!H2:H85,"0"))*100' The problem i have is that while H2 is constant, H85 can change depending on how many rows their are? I am trying to use a terminating value such a 'end' so it knows that when i reach 'end' it uses the previous row reference to replace what is currently 'H85'. Is it possible? Hope this makes sense. |
#3
|
|||
|
|||
Hi,
=(COUNTIF(Calc!H:H,"<15"))/(COUNTIF(Calc!H:H,"0"))*100 I have a question about your formula. It calculates the percentage of the number of cells that are less than 15 (this INCLUDES those that are negative as well as those that are equal to zero) relative to the number of cells that are greater than zero (this EXCLUDES those that are negative or zero). So you can end up with greater than 100% for your result, if there are a lot of negative or zero values (unless that's how you wnt the result or ALL values are greater than zero; in the latter case, the logical function in the denominator is unnecessary - you can just use "COUNT(H:H)") Of course you know your problem better than others. Nonetheless, how about the following formula? =((COUNTIF(Calc!H:H,"<"&15)-COUNTIF(Calc!H:H,"<="&0))/COUNTIF(Calc!H:H,""&0))*100 This will calculate the percentage of values that are 0 AND <15 relative to those that are 0 (so all zero and negative values are totally ignored). Regards, B. R. Ramachandran "dave" wrote: I have a spreadsheet that uses the following calculation:- '=(COUNTIF(Calc!H2:H85,"<15"))/(COUNTIF(Calc!H2:H85,"0"))*100' The problem i have is that while H2 is constant, H85 can change depending on how many rows their are? I am trying to use a terminating value such a 'end' so it knows that when i reach 'end' it uses the previous row reference to replace what is currently 'H85'. Is it possible? Hope this makes sense. |
#4
|
|||
|
|||
Why not just use
=(COUNTIF(Calc!H2:H65536,"<15"))/(COUNTIF(Calc!H2:H65536,"0"))*100 -- HTH RP (remove nothere from the email address if mailing direct) "dave" wrote in message ... I have a spreadsheet that uses the following calculation:- '=(COUNTIF(Calc!H2:H85,"<15"))/(COUNTIF(Calc!H2:H85,"0"))*100' The problem i have is that while H2 is constant, H85 can change depending on how many rows their are? I am trying to use a terminating value such a 'end' so it knows that when i reach 'end' it uses the previous row reference to replace what is currently 'H85'. Is it possible? Hope this makes sense. |
#5
|
|||
|
|||
That's odd but it doesn't for me. Do you have zeroes or some formula in
there? -- HTH RP (remove nothere from the email address if mailing direct) "dave" wrote in message ... Unfortunatley i've tried that, it distorts the figures as it tries to calculate the % for the whole table not just to were the data ends. "Bob Phillips" wrote: Why not just use =(COUNTIF(Calc!H2:H65536,"<15"))/(COUNTIF(Calc!H2:H65536,"0"))*100 -- HTH RP (remove nothere from the email address if mailing direct) "dave" wrote in message ... I have a spreadsheet that uses the following calculation:- '=(COUNTIF(Calc!H2:H85,"<15"))/(COUNTIF(Calc!H2:H85,"0"))*100' The problem i have is that while H2 is constant, H85 can change depending on how many rows their are? I am trying to use a terminating value such a 'end' so it knows that when i reach 'end' it uses the previous row reference to replace what is currently 'H85'. Is it possible? Hope this makes sense. |
#6
|
|||
|
|||
Cheers for that, i'll give it ago and see what happens. Thanks for the help
"B. R.Ramachandran" wrote: Hi, =(COUNTIF(Calc!H:H,"<15"))/(COUNTIF(Calc!H:H,"0"))*100 I have a question about your formula. It calculates the percentage of the number of cells that are less than 15 (this INCLUDES those that are negative as well as those that are equal to zero) relative to the number of cells that are greater than zero (this EXCLUDES those that are negative or zero). So you can end up with greater than 100% for your result, if there are a lot of negative or zero values (unless that's how you wnt the result or ALL values are greater than zero; in the latter case, the logical function in the denominator is unnecessary - you can just use "COUNT(H:H)") Of course you know your problem better than others. Nonetheless, how about the following formula? =((COUNTIF(Calc!H:H,"<"&15)-COUNTIF(Calc!H:H,"<="&0))/COUNTIF(Calc!H:H,""&0))*100 This will calculate the percentage of values that are 0 AND <15 relative to those that are 0 (so all zero and negative values are totally ignored). Regards, B. R. Ramachandran "dave" wrote: I have a spreadsheet that uses the following calculation:- '=(COUNTIF(Calc!H2:H85,"<15"))/(COUNTIF(Calc!H2:H85,"0"))*100' The problem i have is that while H2 is constant, H85 can change depending on how many rows their are? I am trying to use a terminating value such a 'end' so it knows that when i reach 'end' it uses the previous row reference to replace what is currently 'H85'. Is it possible? Hope this makes sense. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculation Setting in Excel | Excel Discussion (Misc queries) | |||
problem office assistant | Excel Discussion (Misc queries) | |||
Slight problem automating Excel in a service | Setting up and Configuration of Excel | |||
Freeze Pane problem in shared workbooks | Excel Discussion (Misc queries) | |||
Baffling formula problem | Excel Discussion (Misc queries) |