choose statement

[Rodney]

I have a problem in a choose statement
that gives #VALUE!

=choose(find(fruit,"NUTPCHFIGSTRAPP",0)/3,3.2,4.1,3.2,1.2,4.5)

What's triggering the error please?

 

[Peo Sjoblom]

For one thing if FIND does not find the string it will return a value error

--


No private emails please, for everyone's
benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom

 

[Rodney]

But the string <is> there.
every cell has a name of one sort or the other in the find list.
Every cell down the rows returns a value error.
There is a problem in the statement somewhere.

I have 800 rows working as a database
so question the range name (fruit) rather than a cell address
fruit is formatted as general
--
(e-mail address removed)
(Remove gum to reply)


| For one thing if FIND does not find the string it will return a value error
|
| --
|
|
| No private emails please, for everyone's
| benefit keep the discussion in the newsgroup.
|
| Regards,
|
| Peo Sjoblom
|
| | > I have a problem in a choose statement
| > that gives #VALUE!
| >
| > =choose(find(fruit,"NUTPCHFIGSTRAPP",0)/3,3.2,4.1,3.2,1.2,4.5)
| >
| > What's triggering the error please?
| >
| >
| >
| >
|
|

 

[CLR]

I'm not real familiar with those functions, but it seems to work if you
delete the quotation marks and change the 0 to 1, as

=CHOOSE(FIND(fruit,nutpchfigstrapp,1)/3,3.2,4.1,3.2,1.2,4.5)

At least it don't return an error message <g>

Vaya con Dios,
Chuck, CABGx3

 

[Rodney]

Thanks Chuck,
I'll have to get back to you,
I was going to try your solution, but the excel sheet won't open
anymore, fails to open under a "page fault"
so I'll have to re build the blighter all over again (sigh) (236 columns)

I seem to know where the problem lies, when selecting a "find"
from an array by dividing by 3, MSWorks finds and allocates the
first by allocating a number 1.
However, in Excel when the find searches for the first divided by 3
it rounds <DOWN> so 1 divided by 3 =.3333 so excel returns 0
this will not select my first Choose option, and returns a fail (value) indicator



--
(e-mail address removed)
(Remove gum to reply)


| I'm not real familiar with those functions, but it seems to work if you
| delete the quotation marks and change the 0 to 1, as
|
| =CHOOSE(FIND(fruit,nutpchfigstrapp,1)/3,3.2,4.1,3.2,1.2,4.5)
|
| At least it don't return an error message <g>
|
| Vaya con Dios,
| Chuck, CABGx3
|
|
| | > I have a problem in a choose statement
| > that gives #VALUE!
| >
| > =choose(find(fruit,"NUTPCHFIGSTRAPP",0)/3,3.2,4.1,3.2,1.2,4.5)
| >
| > What's triggering the error please?
| >
| >
| >
| >
|
|

 

[CLR]

You're welcome Rodney..........sorry you lost your work..........I feel your
pain.........I wasn't real good about backing my stuff up either untill I
went to work at a company that has multiple Network hits each
day........that'll cure anybody <G>........good luck!

Vaya con Dios,
Chuck, CABGx3

 

[Rodney]

Thanks for those comments of comfort Chuck.
I am a bit of a muggle, but I do back up,
I found a copy on my new external HDD
so I just lost the days work (albeit quite considerable)

Your advice offered little resolution
now I get #NAME? errors instead

I've tried using cell locations instead, with no luck.

I'll attempt to get back to the formula which defaulted just on the
first find triplet, and see if someone here can offer advice on
inserting a "ROUND" (rounding up) option to it.

Cheers



| You're welcome Rodney..........sorry you lost your work..........I feel your
| pain.........I wasn't real good about backing my stuff up either untill I
| went to work at a company that has multiple Network hits each
| day........that'll cure anybody <G>........good luck!
|
| Vaya con Dios,
| Chuck, CABGx3
|
|
| | > Thanks Chuck,
| > I'll have to get back to you,
| > I was going to try your solution, but the excel sheet won't open
| > anymore, fails to open under a "page fault"
| > so I'll have to re build the blighter all over again (sigh) (236 columns)
| >
| > I seem to know where the problem lies, when selecting a "find"
| > from an array by dividing by 3, MSWorks finds and allocates the
| > first by allocating a number 1.
| > However, in Excel when the find searches for the first divided by 3
| > it rounds <DOWN> so 1 divided by 3 =.3333 so excel returns 0
| > this will not select my first Choose option, and returns a fail (value)
| indicator
| >
| >
| >
| > --
| > (e-mail address removed)
| > (Remove gum to reply)
| >
| >
| | > | I'm not real familiar with those functions, but it seems to work if you
| > | delete the quotation marks and change the 0 to 1, as
| > |
| > | =CHOOSE(FIND(fruit,nutpchfigstrapp,1)/3,3.2,4.1,3.2,1.2,4.5)
| > |
| > | At least it don't return an error message <g>
| > |
| > | Vaya con Dios,
| > | Chuck, CABGx3
| > |
| > |
| > | | > | > I have a problem in a choose statement
| > | > that gives #VALUE!
| > | >
| > | > =choose(find(fruit,"NUTPCHFIGSTRAPP",0)/3,3.2,4.1,3.2,1.2,4.5)
| > | >
| > | > What's triggering the error please?
| > | >
| > | >
| > | >
| > | >
| > |
| > |
| >
| >
|
|

 

[Peo Sjoblom]

It would be easier if you could say what it is you are trying to do and what
the expected result should be?

--


No private emails please, for everyone's
benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom

 

[Rodney]

Maybe so Peo,
I thought, it would be easier for all if I just offered the nut of the problem
in simplified form.

I was correct, as it turned out, that the difference between MSWorks and Excel
is the "Rounding"

I have solved the problem, after a bit of mucking around by the formula

=ROUNDUP(FIND(STATE,"W ANSWTASQUEN TS AVICN ZACT")/3,0)

It is a part formula for calculating the time difference between the States
of Australia if and when thay employ daylight saving.
When a location arrives in my database, I need to explore where the location
lies and hence the current time.

Thanks for your application to my problem,
however I must say I found your initial response rather
niggardly in it's length.
We, (or at least I) are new users and I would think we need
some explanation towards solutions, even if those with more
experience would find it perhaps "understood"
Rgds





| It would be easier if you could say what it is you are trying to do and what
| the expected result should be?

 

[Peo Sjoblom]

Thanks for your application to my problem,

however I must say I found your initial response rather
niggardly in it's length.
We, (or at least I) are new users and I would think we need
some explanation towards solutions, even if those with more
experience would find it perhaps "understood"
Rgds

A niggardly post sometimes gets a niggardly reply.
As an example, nowhere did you mention that the formula was created in
Works.



--
No private emails please, for everyone's
benefit keep the discussion in the newsgroup.


Regards,

Peo Sjoblom

 

[Rodney]

Yes, you're right. I'll take that on board next time.

I did think though, as a defence, that the formula posted (be it either works or excel)
would have spoken volumes for what I had wanted to achieve, the desire
residing amongst the statement posted.

| A niggardly post sometimes gets a niggardly reply.
| As an example, nowhere did you mention that the formula was created in
| Works.
|