Excel 2007 : A nice formula need to be adjusted to count properly

Hi guys,

I've made a worksheet with a several formulas which depend one another.
One of them fails to return the correct results if a blank cell involved.
This formula had been provided by a forum mastermind for another project, which means I don't know how to adjusted/modified
for the simple reason that I don't understand how it works.

So,
What I need is an expert to add/withdrawl few tweaks and make it work properly in the new project.

It would be greatly appreciated any help.

Nope,

It doesn't work.
But you know, I'm pretty sure (I think) that the formula in F column doesn't have a problem.
Tweaks needed in formulas of G & H columns.

Thanks anyway

Dear all,

Responding to messages for more details let me describe our problem more extensively.
The problem as it seems right now is in the formulas of columns F, G & H (note that G&H columns contain the same formula). The rest of the worksheet depends on the outcome of those formulas.

Description
The F column formula, counts the number of contracts where participated in the shown transactions.
What it does is the counting of the changes in the contract code number in column B. In other words as long as the contract code number in B column is the same the formula does not change the counting in F column, e.g. uploaded file - Example 1, cells B3,B4,B5 - F3,F4,F5. As you can see in the same example, when the contract code number change in cell B6, then the counting in cell F6 changes as well.

Basically the returning outcome of this formula is the ascending numbering of the contracts where participated in these transactions. We believe that nothing is wrong with this formula. From what we saw and check, works perfectly. (Of course, this assessment has been made by us, using our level of knowledge)

The G column formula, counts the positive values of column C and the H column formula counts the negative values of the same column (C). In other words in the cells of column G we have the increasing numbering of positive values from column C, while in the cells of column H we have the increasing numbering of negative values from the same column (C).

The problem begins if one of the cells of C column is empty. Sometimes it happens and we cannot eliminate this possibility. To be exact, it can be occurred only when more than one transaction has been done under the same contract code number. That means, that sometimes, 2 or 3 transactions can be made under the same contract code number and in 1 of them, the result could be nothing (caution: not zero (0) but a simple nothing (""), which means we cannot put a zero (0) to solve the problem because the zero (0) is another possible outcome).

When in column C an empty cell appears the worksheet collapses, because the formulas don’t count the next one in which always will be a value. In a nutshell, empty cells are rare, but they appear from time to time and when they do, they appear alone (they will be never consecutive).

Solution
We think that the formula surgery has to be made in the formulas of G&H columns. They need to include in their calculation the possibility of an empty cell.
The real problem is, that these formulas had been provided to us by one of the forum masterminds for another occasion (where worked perfectly). His level of knowledge is way ahead of us, thus we cannot modify his formula (we don’t understand how it works) and make it work for the present use. We believe that these formulas need few tweaks in order to be adjusted to added requirement of the empty cell. The only difference between our 2 occasions is that in the first one we didn’t have the nothing ("") as possible outcome.

To make it easier comprehended, we had upload in the beginning of the thread, a file with a scenario in 2 versions.
In the first version of the scenario in sheet “Example 1”, you can see how the formulas work, when all the cells in column C contain values.
On the contrary, in the second version of the scenario in sheet “Example 2”, you can see how the formulas fail, when one of the cells in column C is empty.

We hope this extensive analysis of our problem, can help those who want to help.