First - With the formulas that have been given so far make sure the
names in column A are actually deleted or your weights will be off. We
have users here that hit the space bar to "delete" something instead of
hitting the delete key. If this is done in your file with the formulas
given your will still get 90 in the weight column because a space in a
cell is not the same as an empty or blank cell. If you want to safe
guard against this you can use the formula "=if(len(trim(a2))>1,90,0)"
without quotes. This reads, if the lenght of the trimed value in cell
a2 is greater than 1 then enter 90 in the current cell otherwise enter
0. Trim removes all spaces from text except for single spaces between
words. This assumes your passenger names will always be greater in
length than a single character. If you prefer a clean looking
spreadsheet you can enter a double quote instead of 0 at the end of the
formula, i.e. =if(len(a2)>1,90,""), so you end up with a blank cell
when there is no name instead of a 0.
Second - Yakymakha, when it was suggested to "Put the default weight 90
kg in e.g. cell B1." the default refered to was the 90 kg from the
orginal spec. In this case you would enter 90 into cell B1 and the
formula =IF(ISBLANK(A2);0;$B$1) would use the value in cell B1 even
when copied down to other cells. The $ in front of B and 1 make it an
absolute cell reference instead of a relative cell reference. That way
if the default weight changes from 90 to 100 the user would only have
to change the value in cell B1 and it would be updated through the rest
of the spreadsheet.
On the one hand using this method makes it easier to make changes in
the default value for weight flow through the spreadsheet but on the
otherhand, if someone were to make a mistake and change the value in
cell B1 by accident then the weight calculation would be wrong. That
might not be noticed and might have dire consequences.
Regards,
Mike
--- "Yakymakha L. Olexandr" <[log in to unmask]> wrote:
> Hello Goran,
>
> Sunday, April 21, 2002, 7:43:29 PM, you wrote:
>
> GH> Mick,
>
>
> GH> I am not an Excel wizard, however I experimented with the
> formulas for a
> GH> while, and came up with the following:
>
> So do I.
> GH> Suppose you put people's names in column A.
> GH> Suppose you put people's wights in column B.
> GH> Put the default weight 90 kg in e.g. cell B1.
>
> I don't know how to use the default value in Excel 97.
> In Access this can be easy done. Try to explain in detail.
>
> GH> In cell B2 write the formula: =IF(ISBLANK(A2);0;$B$1)
> GH> It should be read "if the cell in column A (in the same row) is
> blank,
> GH> return 0, else return the value in cell B1, i.e. 90 kg".
> Important: The
> GH> dollar signs means an absolute cell reference.
>
> Sorry, but this formula =IF(ISBLANK(A2);0;$B$1) doesn't work in
> Excel 97 (?).
>
>
>
>
>
> --
> Best regards,
> Yakymakha
>
> PCSOFT maintains many useful files for download
> visit our download web page at:
> http://freepctech.com/downloads.shtml
__________________________________________________
Do You Yahoo!?
Yahoo! Games - play chess, backgammon, pool and more
http://games.yahoo.com/
Curious about the people moderating your
messages? Visit our staff web site:
http://freepctech.com/staff.shtml
|
