I guess someone here could provide some links to most kinds of
documentation. I use the built in help functions and it's usually enough.
If not I search with google and surprisingly often I find the answers. If
not, I ask here...
When I am not sure about what cell function to use, I press Shift+F2 to
open a dialog from where I can reach all available functions (except those
I defined myself). Clicking a function provides me with brief information
of what it does, and klicking the help button takes me to the built in
help about the selected function.
Unfortunately it happens now and then that the help text is translated
wrong (to Swedish in my case) and some examples might not work properly,
just because of the incorrect translations. I have one example about
formatting using the cell format dialog. Here's what it says about it, in
Swedish:
Om du t.ex. vill använda olika färger för olika temperaturer skriver du
följande:
###[<=4][GRÖN]#.##0,00;[>7][RÖD]#.##0,00;[BLÅ]#.##0,00
Alla temperaturer under noll är blåa, temperaturer mellan 0 och 30° C är
svarta och temperaturer över 30° C är röda.
Translation:
If you, for example, want to use different colours for different
temperatures, type the following:
###[<=4][GRÖN]#.##0,00;[>7][RÖD]#.##0,00;[BLÅ]#.##0,00
All temperatures below zero are blue, temperatures between 0 and 30° C are
black and temperatures above 30° C are red.
This does not work, of course. First of all the temperatues mentioned are
not the same as those in the example. As if that is not enough, they
translated the colours to Swedish in the formula.
The following works in Swedish OOo 1.1.4, just like the text describes:
[<0][GREEN]# ##0,00;[>30][RED]# ##0,00;[BLUE]# ##0,00
Maybe this one is even better:
[<0][GREEN]# ##0,00"°C";[>30][RED]# ##0,00"°C";[BLUE]# ##0,00"°C"
It took me quite some time to come up with the idea of typing the colours
in English, when the example above failed all the time... The fact that I
had to remove the first three characters (###) and some dots was more
obvious.
By the way, I couldn't find this feature at all in Excel. Not that I care
that much, but does anyone here know if it exists at all in Excel (apart
from that you can have different formats for positive, negative and zero
values)?
Johnny
GregChi <[EMAIL PROTECTED]> skrev den Fri, 20 May 2005 20:26:24 -0400:
On Sat, 21 May 2005 00:10:12 +0200, Johnny Andersson wrote:
GregChi <[EMAIL PROTECTED]> skrev den Fri, 20 May 2005 15:46:01 -0400:
In a check register I want a formula in cell A1 to show the last
value in the column(D?) that is the current balance. As more data is
entered this last item will be shifted down. Until data is entered the
cell is empty.
Is there a function/formula for this?
I have two ideas:
1. This will only work if there are no empty rows between those rows
with
data.
Enter the following to A1:
=INDIRECT(ADDRESS(COUNTA(D1:D32000);4))
If, for example, the first non empty cell in column D is D2, just modify
the formula as follows:
=INDIRECT(ADDRESS(COUNTA(D1:D32000)+1;4))
COUNTA(D1:D32000)+1 represent the last used row in this case.
4 represents column D, of course.
2. This will work in all cases I can think of right now, but I guess it
will work slower and the file size will be larger:
Insert a new E column (which you can hide later). Enter the following
into
E2, if D2 is the first cell containing relevant data:
=IF(D3="";D2;0)
Copy this as far down as you think is necessary. For example, E1000
should
contain =IF(D1001="";D1000;0) and so on...
Enter the following in A1:
=SUM(E1:E32000)
If your D column ONLY contains POSITIVE values, =MAX(E1:E32000) also
will
work, but why try that when SUM works in all cases?
Now, hide your E column and it will look nicer.
If method 1 works for you, I guess that's the one to use. Use method two
ONLY if method 1 for some reason can't be used, like if there are empty
cells here and there in column D.
I can also think of a third solution to this problem: Writing a new cell
function in StarBasic and use it in A1.
Thanks for your reply.
Are there docs for this kind of function?
I searched in the helps for this stuff. (doesn't mean its' not there
:-) )
I'll try these.....
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
