Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
And then there were ZEROs...
HELP, i'm going bonkers with zeroes. I've actually posted this quite a while
back, and worked on various suggested formulas but to no avail. I hope you can be patient as i will have to explain quite elaborately due to the nature of the required results. Here's what been bugging me (its for a football statistics): A B C -0.5 1 0 +0.5 0 0 +0.5 0 1 +0.5 1 1 -0.5 0 0 -0.5 1 0 +0.5 0 2 -0.5 0 1 -0.5 0 3 +0.5 1 1 I would like to count the following: a) two (2) consecutive 0s are met in column B, with condition that the subsequent row (row 4 for above) if cell A4 is a positive digit (+0.5 in this case). If YES, count the sequence everytime it happens within the column (if NO, don't count). =FORMULA? b) While (a) just count the number of times it occurs, another formula is required to count if condition (a) is met, AND (B4-C4)+A4=+ve number then count how many times such an event happens within the column. For both the above, it must be noted that in the event another 0 appears after the two (2) consecutive 0s (see B7:B9), the formula will still remain and count the subsequent row based on the conditions provided in (a) and (b). Is this possible? HELP! Thanks! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
And then there were ZEROs...
its for a football statistics
Maybe if you explained what this data actually means we might be able to come up with something that's workable. If you recall, I replied to a couple of your previous posts and I had my doubts if what you're asking for is even possible. It looks like my doubts have proven to be accurate! <g Lori also replied and between us we couldn't come up with anything that worked. Lori is a top-notch formula person! I'm not a slouch, either! -- Biff Microsoft Excel MVP "andrew" wrote in message ... HELP, i'm going bonkers with zeroes. I've actually posted this quite a while back, and worked on various suggested formulas but to no avail. I hope you can be patient as i will have to explain quite elaborately due to the nature of the required results. Here's what been bugging me (its for a football statistics): A B C -0.5 1 0 +0.5 0 0 +0.5 0 1 +0.5 1 1 -0.5 0 0 -0.5 1 0 +0.5 0 2 -0.5 0 1 -0.5 0 3 +0.5 1 1 I would like to count the following: a) two (2) consecutive 0s are met in column B, with condition that the subsequent row (row 4 for above) if cell A4 is a positive digit (+0.5 in this case). If YES, count the sequence everytime it happens within the column (if NO, don't count). =FORMULA? b) While (a) just count the number of times it occurs, another formula is required to count if condition (a) is met, AND (B4-C4)+A4=+ve number then count how many times such an event happens within the column. For both the above, it must be noted that in the event another 0 appears after the two (2) consecutive 0s (see B7:B9), the formula will still remain and count the subsequent row based on the conditions provided in (a) and (b). Is this possible? HELP! Thanks! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
And then there were ZEROs...
On Mon, 4 Aug 2008 20:29:01 -0700, andrew
wrote: HELP, i'm going bonkers with zeroes. I've actually posted this quite a while back, and worked on various suggested formulas but to no avail. I hope you can be patient as i will have to explain quite elaborately due to the nature of the required results. Here's what been bugging me (its for a football statistics): A B C -0.5 1 0 +0.5 0 0 +0.5 0 1 +0.5 1 1 -0.5 0 0 -0.5 1 0 +0.5 0 2 -0.5 0 1 -0.5 0 3 +0.5 1 1 I would like to count the following: a) two (2) consecutive 0s are met in column B, with condition that the subsequent row (row 4 for above) if cell A4 is a positive digit (+0.5 in this case). If YES, count the sequence everytime it happens within the column (if NO, don't count). =FORMULA? b) While (a) just count the number of times it occurs, another formula is required to count if condition (a) is met, AND (B4-C4)+A4=+ve number then count how many times such an event happens within the column. For both the above, it must be noted that in the event another 0 appears after the two (2) consecutive 0s (see B7:B9), the formula will still remain and count the subsequent row based on the conditions provided in (a) and (b). Is this possible? HELP! Thanks! I don't understand b). Please explain. What is "+ve number" ? I don't understand what you mean by "the formula will still remain and count the subsequent row..." in the note. Please explain. I am not sure that I have understood a) correctly, but anyway Try this formula for a): Note: This is an array formula that has to be entered by CTRL+SHIFT+ENTER rather than just ENTER. =SUMPRODUCT(- -(B$1:B$10=0),- -(B$2:B$11=0),- -(- -A$3:A$120)) This gives the result 2 for the test data you have provided Hope this helps / Lars-Åke |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
And then there were ZEROs...
Hi Biff, firstly thanks for being patient and helping me out with your
various formulas. You've been great! Lori too, and she was close the last time... Anyway, let me see if i can explain clearer. What i intend to do from the table is to analyse for sequences of zeros. What the table shows are the results of matches played by a team. The +0.5 or -0.5 are just handicaps that the team provides to its opponent. The results are updated weekly in a column from the fixtures played over the year. Now, here's the actual part of analysis which i'm trying to derive with formulas: 1) To check for sequences of two (2) zeroes on column B (score of home team) with the conditions provided in (a) per original post. As for (b), it is just a total count of the occurence regardless if the result [(column B minus column C) + column A] is a positive value or not. So, as an example, if i were to refer to the original table posted, B2 and B3 had 0s (i.e. the team did not score consecutively in 2 matches). On B4 (subsequent row), the team played to a 1-1 draw, with a handicap of +0.5. From this example, the formula will count this as 1 instance as it returned a positive value ,i.e. (1-1)+0.5 = +0.5 IF the result had been 1-2 for row B4 and C4, then it return a negative value, i.e. (1-2)+0.5 = -0.5 (which the formula will not count as 1 instance) If the above is possible, then i'm actually looking at a table that stores the following information: i) sequence of two 0s, with +0.5 handicap (another column will store the total count of such occurence regardless if the subsequent row returned a positive or negative value for row (B-C)+A) ii) sequence of two 0s, giving -0.5 handicap Does the above make sense? If not, i can e-mail you a sample sheet with the actual data and formulas....Thanks in advance! "T. Valko" wrote: its for a football statistics Maybe if you explained what this data actually means we might be able to come up with something that's workable. If you recall, I replied to a couple of your previous posts and I had my doubts if what you're asking for is even possible. It looks like my doubts have proven to be accurate! <g Lori also replied and between us we couldn't come up with anything that worked. Lori is a top-notch formula person! I'm not a slouch, either! -- Biff Microsoft Excel MVP "andrew" wrote in message ... HELP, i'm going bonkers with zeroes. I've actually posted this quite a while back, and worked on various suggested formulas but to no avail. I hope you can be patient as i will have to explain quite elaborately due to the nature of the required results. Here's what been bugging me (its for a football statistics): A B C -0.5 1 0 +0.5 0 0 +0.5 0 1 +0.5 1 1 -0.5 0 0 -0.5 1 0 +0.5 0 2 -0.5 0 1 -0.5 0 3 +0.5 1 1 I would like to count the following: a) two (2) consecutive 0s are met in column B, with condition that the subsequent row (row 4 for above) if cell A4 is a positive digit (+0.5 in this case). If YES, count the sequence everytime it happens within the column (if NO, don't count). =FORMULA? b) While (a) just count the number of times it occurs, another formula is required to count if condition (a) is met, AND (B4-C4)+A4=+ve number then count how many times such an event happens within the column. For both the above, it must be noted that in the event another 0 appears after the two (2) consecutive 0s (see B7:B9), the formula will still remain and count the subsequent row based on the conditions provided in (a) and (b). Is this possible? HELP! Thanks! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
And then there were ZEROs...
i can e-mail you a sample sheet with the actual data and formulas
OK, let's see what we're dealing with! I'm at: xl can help at comcast period net Remove "can" and change the obvious. -- Biff Microsoft Excel MVP "andrew" wrote in message ... Hi Biff, firstly thanks for being patient and helping me out with your various formulas. You've been great! Lori too, and she was close the last time... Anyway, let me see if i can explain clearer. What i intend to do from the table is to analyse for sequences of zeros. What the table shows are the results of matches played by a team. The +0.5 or -0.5 are just handicaps that the team provides to its opponent. The results are updated weekly in a column from the fixtures played over the year. Now, here's the actual part of analysis which i'm trying to derive with formulas: 1) To check for sequences of two (2) zeroes on column B (score of home team) with the conditions provided in (a) per original post. As for (b), it is just a total count of the occurence regardless if the result [(column B minus column C) + column A] is a positive value or not. So, as an example, if i were to refer to the original table posted, B2 and B3 had 0s (i.e. the team did not score consecutively in 2 matches). On B4 (subsequent row), the team played to a 1-1 draw, with a handicap of +0.5. From this example, the formula will count this as 1 instance as it returned a positive value ,i.e. (1-1)+0.5 = +0.5 IF the result had been 1-2 for row B4 and C4, then it return a negative value, i.e. (1-2)+0.5 = -0.5 (which the formula will not count as 1 instance) If the above is possible, then i'm actually looking at a table that stores the following information: i) sequence of two 0s, with +0.5 handicap (another column will store the total count of such occurence regardless if the subsequent row returned a positive or negative value for row (B-C)+A) ii) sequence of two 0s, giving -0.5 handicap Does the above make sense? If not, i can e-mail you a sample sheet with the actual data and formulas....Thanks in advance! "T. Valko" wrote: its for a football statistics Maybe if you explained what this data actually means we might be able to come up with something that's workable. If you recall, I replied to a couple of your previous posts and I had my doubts if what you're asking for is even possible. It looks like my doubts have proven to be accurate! <g Lori also replied and between us we couldn't come up with anything that worked. Lori is a top-notch formula person! I'm not a slouch, either! -- Biff Microsoft Excel MVP "andrew" wrote in message ... HELP, i'm going bonkers with zeroes. I've actually posted this quite a while back, and worked on various suggested formulas but to no avail. I hope you can be patient as i will have to explain quite elaborately due to the nature of the required results. Here's what been bugging me (its for a football statistics): A B C -0.5 1 0 +0.5 0 0 +0.5 0 1 +0.5 1 1 -0.5 0 0 -0.5 1 0 +0.5 0 2 -0.5 0 1 -0.5 0 3 +0.5 1 1 I would like to count the following: a) two (2) consecutive 0s are met in column B, with condition that the subsequent row (row 4 for above) if cell A4 is a positive digit (+0.5 in this case). If YES, count the sequence everytime it happens within the column (if NO, don't count). =FORMULA? b) While (a) just count the number of times it occurs, another formula is required to count if condition (a) is met, AND (B4-C4)+A4=+ve number then count how many times such an event happens within the column. For both the above, it must be noted that in the event another 0 appears after the two (2) consecutive 0s (see B7:B9), the formula will still remain and count the subsequent row based on the conditions provided in (a) and (b). Is this possible? HELP! Thanks! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
And then there were ZEROs...
Err, i don't see any e-mail addy below except in your profile (which i tried
to send but mail bounced..). Can you send a test e-mail to me so that i can reply with the data for your review? Thanks! "T. Valko" wrote: i can e-mail you a sample sheet with the actual data and formulas OK, let's see what we're dealing with! I'm at: xl can help at comcast period net Remove "can" and change the obvious. -- Biff Microsoft Excel MVP "andrew" wrote in message ... Hi Biff, firstly thanks for being patient and helping me out with your various formulas. You've been great! Lori too, and she was close the last time... Anyway, let me see if i can explain clearer. What i intend to do from the table is to analyse for sequences of zeros. What the table shows are the results of matches played by a team. The +0.5 or -0.5 are just handicaps that the team provides to its opponent. The results are updated weekly in a column from the fixtures played over the year. Now, here's the actual part of analysis which i'm trying to derive with formulas: 1) To check for sequences of two (2) zeroes on column B (score of home team) with the conditions provided in (a) per original post. As for (b), it is just a total count of the occurence regardless if the result [(column B minus column C) + column A] is a positive value or not. So, as an example, if i were to refer to the original table posted, B2 and B3 had 0s (i.e. the team did not score consecutively in 2 matches). On B4 (subsequent row), the team played to a 1-1 draw, with a handicap of +0.5. From this example, the formula will count this as 1 instance as it returned a positive value ,i.e. (1-1)+0.5 = +0.5 IF the result had been 1-2 for row B4 and C4, then it return a negative value, i.e. (1-2)+0.5 = -0.5 (which the formula will not count as 1 instance) If the above is possible, then i'm actually looking at a table that stores the following information: i) sequence of two 0s, with +0.5 handicap (another column will store the total count of such occurence regardless if the subsequent row returned a positive or negative value for row (B-C)+A) ii) sequence of two 0s, giving -0.5 handicap Does the above make sense? If not, i can e-mail you a sample sheet with the actual data and formulas....Thanks in advance! "T. Valko" wrote: its for a football statistics Maybe if you explained what this data actually means we might be able to come up with something that's workable. If you recall, I replied to a couple of your previous posts and I had my doubts if what you're asking for is even possible. It looks like my doubts have proven to be accurate! <g Lori also replied and between us we couldn't come up with anything that worked. Lori is a top-notch formula person! I'm not a slouch, either! -- Biff Microsoft Excel MVP "andrew" wrote in message ... HELP, i'm going bonkers with zeroes. I've actually posted this quite a while back, and worked on various suggested formulas but to no avail. I hope you can be patient as i will have to explain quite elaborately due to the nature of the required results. Here's what been bugging me (its for a football statistics): A B C -0.5 1 0 +0.5 0 0 +0.5 0 1 +0.5 1 1 -0.5 0 0 -0.5 1 0 +0.5 0 2 -0.5 0 1 -0.5 0 3 +0.5 1 1 I would like to count the following: a) two (2) consecutive 0s are met in column B, with condition that the subsequent row (row 4 for above) if cell A4 is a positive digit (+0.5 in this case). If YES, count the sequence everytime it happens within the column (if NO, don't count). =FORMULA? b) While (a) just count the number of times it occurs, another formula is required to count if condition (a) is met, AND (B4-C4)+A4=+ve number then count how many times such an event happens within the column. For both the above, it must be noted that in the event another 0 appears after the two (2) consecutive 0s (see B7:B9), the formula will still remain and count the subsequent row based on the conditions provided in (a) and (b). Is this possible? HELP! Thanks! |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
And then there were ZEROs...
i don't see any e-mail addy below
No, it's there. It's just disguised to keep the spam trawlers from recognizing it. Here it is again: xl can help at comcast period net Rmove the word "can". Replace "at" and "period" with their respective symbols and remove all the spaces. -- Biff Microsoft Excel MVP "andrew" wrote in message ... Err, i don't see any e-mail addy below except in your profile (which i tried to send but mail bounced..). Can you send a test e-mail to me so that i can reply with the data for your review? Thanks! "T. Valko" wrote: i can e-mail you a sample sheet with the actual data and formulas OK, let's see what we're dealing with! I'm at: xl can help at comcast period net Remove "can" and change the obvious. -- Biff Microsoft Excel MVP "andrew" wrote in message ... Hi Biff, firstly thanks for being patient and helping me out with your various formulas. You've been great! Lori too, and she was close the last time... Anyway, let me see if i can explain clearer. What i intend to do from the table is to analyse for sequences of zeros. What the table shows are the results of matches played by a team. The +0.5 or -0.5 are just handicaps that the team provides to its opponent. The results are updated weekly in a column from the fixtures played over the year. Now, here's the actual part of analysis which i'm trying to derive with formulas: 1) To check for sequences of two (2) zeroes on column B (score of home team) with the conditions provided in (a) per original post. As for (b), it is just a total count of the occurence regardless if the result [(column B minus column C) + column A] is a positive value or not. So, as an example, if i were to refer to the original table posted, B2 and B3 had 0s (i.e. the team did not score consecutively in 2 matches). On B4 (subsequent row), the team played to a 1-1 draw, with a handicap of +0.5. From this example, the formula will count this as 1 instance as it returned a positive value ,i.e. (1-1)+0.5 = +0.5 IF the result had been 1-2 for row B4 and C4, then it return a negative value, i.e. (1-2)+0.5 = -0.5 (which the formula will not count as 1 instance) If the above is possible, then i'm actually looking at a table that stores the following information: i) sequence of two 0s, with +0.5 handicap (another column will store the total count of such occurence regardless if the subsequent row returned a positive or negative value for row (B-C)+A) ii) sequence of two 0s, giving -0.5 handicap Does the above make sense? If not, i can e-mail you a sample sheet with the actual data and formulas....Thanks in advance! "T. Valko" wrote: its for a football statistics Maybe if you explained what this data actually means we might be able to come up with something that's workable. If you recall, I replied to a couple of your previous posts and I had my doubts if what you're asking for is even possible. It looks like my doubts have proven to be accurate! <g Lori also replied and between us we couldn't come up with anything that worked. Lori is a top-notch formula person! I'm not a slouch, either! -- Biff Microsoft Excel MVP "andrew" wrote in message ... HELP, i'm going bonkers with zeroes. I've actually posted this quite a while back, and worked on various suggested formulas but to no avail. I hope you can be patient as i will have to explain quite elaborately due to the nature of the required results. Here's what been bugging me (its for a football statistics): A B C -0.5 1 0 +0.5 0 0 +0.5 0 1 +0.5 1 1 -0.5 0 0 -0.5 1 0 +0.5 0 2 -0.5 0 1 -0.5 0 3 +0.5 1 1 I would like to count the following: a) two (2) consecutive 0s are met in column B, with condition that the subsequent row (row 4 for above) if cell A4 is a positive digit (+0.5 in this case). If YES, count the sequence everytime it happens within the column (if NO, don't count). =FORMULA? b) While (a) just count the number of times it occurs, another formula is required to count if condition (a) is met, AND (B4-C4)+A4=+ve number then count how many times such an event happens within the column. For both the above, it must be noted that in the event another 0 appears after the two (2) consecutive 0s (see B7:B9), the formula will still remain and count the subsequent row based on the conditions provided in (a) and (b). Is this possible? HELP! Thanks! |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
And then there were ZEROs...
Hi Biff, file has been sent.
"T. Valko" wrote: i don't see any e-mail addy below No, it's there. It's just disguised to keep the spam trawlers from recognizing it. Here it is again: xl can help at comcast period net Rmove the word "can". Replace "at" and "period" with their respective symbols and remove all the spaces. -- Biff Microsoft Excel MVP "andrew" wrote in message ... Err, i don't see any e-mail addy below except in your profile (which i tried to send but mail bounced..). Can you send a test e-mail to me so that i can reply with the data for your review? Thanks! "T. Valko" wrote: i can e-mail you a sample sheet with the actual data and formulas OK, let's see what we're dealing with! I'm at: xl can help at comcast period net Remove "can" and change the obvious. -- Biff Microsoft Excel MVP "andrew" wrote in message ... Hi Biff, firstly thanks for being patient and helping me out with your various formulas. You've been great! Lori too, and she was close the last time... Anyway, let me see if i can explain clearer. What i intend to do from the table is to analyse for sequences of zeros. What the table shows are the results of matches played by a team. The +0.5 or -0.5 are just handicaps that the team provides to its opponent. The results are updated weekly in a column from the fixtures played over the year. Now, here's the actual part of analysis which i'm trying to derive with formulas: 1) To check for sequences of two (2) zeroes on column B (score of home team) with the conditions provided in (a) per original post. As for (b), it is just a total count of the occurence regardless if the result [(column B minus column C) + column A] is a positive value or not. So, as an example, if i were to refer to the original table posted, B2 and B3 had 0s (i.e. the team did not score consecutively in 2 matches). On B4 (subsequent row), the team played to a 1-1 draw, with a handicap of +0.5. From this example, the formula will count this as 1 instance as it returned a positive value ,i.e. (1-1)+0.5 = +0.5 IF the result had been 1-2 for row B4 and C4, then it return a negative value, i.e. (1-2)+0.5 = -0.5 (which the formula will not count as 1 instance) If the above is possible, then i'm actually looking at a table that stores the following information: i) sequence of two 0s, with +0.5 handicap (another column will store the total count of such occurence regardless if the subsequent row returned a positive or negative value for row (B-C)+A) ii) sequence of two 0s, giving -0.5 handicap Does the above make sense? If not, i can e-mail you a sample sheet with the actual data and formulas....Thanks in advance! "T. Valko" wrote: its for a football statistics Maybe if you explained what this data actually means we might be able to come up with something that's workable. If you recall, I replied to a couple of your previous posts and I had my doubts if what you're asking for is even possible. It looks like my doubts have proven to be accurate! <g Lori also replied and between us we couldn't come up with anything that worked. Lori is a top-notch formula person! I'm not a slouch, either! -- Biff Microsoft Excel MVP "andrew" wrote in message ... HELP, i'm going bonkers with zeroes. I've actually posted this quite a while back, and worked on various suggested formulas but to no avail. I hope you can be patient as i will have to explain quite elaborately due to the nature of the required results. Here's what been bugging me (its for a football statistics): A B C -0.5 1 0 +0.5 0 0 +0.5 0 1 +0.5 1 1 -0.5 0 0 -0.5 1 0 +0.5 0 2 -0.5 0 1 -0.5 0 3 +0.5 1 1 I would like to count the following: a) two (2) consecutive 0s are met in column B, with condition that the subsequent row (row 4 for above) if cell A4 is a positive digit (+0.5 in this case). If YES, count the sequence everytime it happens within the column (if NO, don't count). =FORMULA? b) While (a) just count the number of times it occurs, another formula is required to count if condition (a) is met, AND (B4-C4)+A4=+ve number then count how many times such an event happens within the column. For both the above, it must be noted that in the event another 0 appears after the two (2) consecutive 0s (see B7:B9), the formula will still remain and count the subsequent row based on the conditions provided in (a) and (b). Is this possible? HELP! Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
when i megre cells that begin with zeros , I lose the zeros | Excel Discussion (Misc queries) | |||
Essbase: Text zeros to number zeros | Excel Discussion (Misc queries) | |||
Charting zeros as non zeros | Charts and Charting in Excel | |||
save text field w/ leading zeros in .csv format & not lose zeros? | Excel Discussion (Misc queries) | |||
Need Zeros | Excel Worksheet Functions |