In a spreadsheet, items are counted as "in service" or "out of service". Each day a code is entered to indicate the status.
I I I I O O O O I I I O O I I I I I I I I I I O I I I
A formula is needed to count the number of times items are "out of service". So, in the example above, the result of the formula would be 3.
Can anybody help??
Are each of the "I"'s and "O"'s entered into separate cells?
- Clay
Assuming all "O"s and "I"s are in separate cells across row 2 then to count the number of "runs" of "O"s
=(A2="O")+SUMPRODUCT(--(B2:IV2="O"),--(A2:IU2<>"O"))
Yes, they are in seperate cells...
Still having issues with this - does the formula go in A2?
=(A2="O")+SUMPRODUCT(--(B2:IV2="O"),--(A2:IU2<>"O"))
what are the "--" ?
Thanks!
Last edited by HBF; 09-14-2007 at 10:13 AM.
The suggested formula assumes you have your O's and I's in separate cells starting at A2 and going across row2.
The formula would be put anywhere in your sheet except within row 2.
You would have to adjust the cell references to suit your data.
in the formula A2 is the start cell and B2 is the cell next to the start cell...IV2 is the last column in the sheet (this allows coverage of I's and O's to the end of the row).
The -- is known as double unary and is used to coerce results of True's and False's to 1's and 0's, respectively, so that the math can be performed.
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks