A conditional problem?

[tilltek]

Well I think perhaps it's an if() problem.
I have 3 columns, A, B and C labeled “IN”, “OUT” and “BALANCE” in row 1.

So in row 2 I feed last months carry over balance into “2A” and set “2C = 2A”
Then row 3 onwards I set “3C = 2C + 3A – 3B”
(new balance = previous balance plus IN minus OUT)

But how to hide result of that formula if nothing to compute?
That is, if there was no income and no expenditure the formula would be
“new balance = old balance + nil - nil” so the old balance is repeated down the sheet.
How do I show the result of that formula only if there was income or expenditure?

Ken

 

[SkipVought]

hi,

But how to hide result of that formula if nothing to compute?

if you use the TABLE feature in 2007 or the Data > List > Create List feature in 2003 or earlier, to define your table, Your formulas will AUTOMATICALLY be copied to the newt row as soon as you enter data into the row.

CAVEAT: You need the SAME FORMULA in all rows, including row 2...
[tt]
=A2-B2+if(ISNUMBER(C1),C1,0)
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[tilltek]

This might explain better what I'm trying to do.

1: A B C
------------------------------
2: IN | OUT | BALANCE
------------------------------
3: 1,000 | | 1,000 < C3=A3 (this is a carry over balance)
4: | 200 | 800 < C4=C3+A4-B4 (new balance)
5: 100 | | 900 < C5=C4+A5-B5 (this formula is copied C5 ~ C100)
6: | | 900 <
7: | | 900 < How do I stop this formula computing if
8: | | 900 < there is no data in A or B to compute?

Kenny (by the way, I'm using Open Office)

 

[lionelhill]

Skip's approach is best, but I'm not convinced his caveat actually applies quite as strictly as it theoretically should (try it and see if it works; for me, in 2007, I can have a different formula in my first row, row 2, and still get the correct formula filled down).

It's unfortunate that Microsoft chose to call this feature a "table"; changing names (it was previously a "list") is always guaranteed to confuse people, and sadly those less experienced in Excel tend to use words like "table" and "worksheet" interchangeably, and therefore come to grief searching for help. On top of that, Word tables are a completely different thing.

Fill in your columns just as you have, but stop where the data stops
Mark the entire area containing data and formulae
On the "insert" tab, click table, and follow the instructions.
From now on, if you add data at the bottom, the formula will appear miraculously.

If, however, it doesn't work for you, for any reason, try (as a last resort)
=if(A<>"", myfavouriteformulahere, "")

 

[strongm]

>by the way, I'm using Open Office

Then you are probably in the wrong forum ...

 

[MakeItSo]

Tested in OO and working:

=IF(AND(ISEMPTY(A4);ISEMPTY(B4));"";C3+A4-B4)

;-)

[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell

 

[SkipVought]

This is the Microsoft Office forum, forum68.

Open Office is forum904. There are LOTS of differences in features. I gave you a Microsoft Office Excel solution that will NOT work in Open Office!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[tilltek]

Thanks guys and gals, solved it.

I used =IF(A3+B3<>0;C2+A3-B3;0)

In other words, if column A + B are zero then so is column C
Otherwise do the sum and put it in C.

Thanks all.

 

[MakeItSo]

Umm Problem:
After the fiurst non-change, your balance will be zero!
How do you compute the next occurring change?

[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell

 

[SkipVought]

WHY would you have ZEROS as values in a row?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}