Help with Isnumber * iserror

[Superstar31]

Not sure how to word this, so i'll first port my formula

=IF(ISNUMBER(E129)*not(ISERROR(E129)),'LCDM Revised'!H129,"")

To me my formula reads is there a number in e129? Yes
Is there a error message in e129? NO
OK, since there is a number and no error message, copy h129 to my location

Now my formula works if there is no error message, but if there is a error message it still gives me line h129

I've even tried, changing "NOT" to "AND" but no matter what I try, I get a number even if there is an error message? anyone got any ideas? or where I can look for some?

Also, where can i find a list of all variables? like *,/,+,- and all the if statements i can think of?

Thanks

Almost forgot using Excel 2000

 

[Superstar31]

I'm not sure I understand.

Are you trying to return "9876" in the last column, but only if column E is neither a number, nor an error? Or are you trying to trap the errors that appear in column G (i.e. your #N/A, in this sample)?

At the moment the formula is ONLY evaluating the values in column E (i.e. the repeated $12.34 value). Thus, you will always get the same result on every row.

If you are trying to evaluate both the values in column E (for numerical properties) and the values in column G (for validity), then simply change the reference in the second portion of the formula, as follows:

=IF(AND(ISNUMBER(E129),NOT(ISERROR(G129))),'LCDM Revised'!H129,"")

Am I helping or hindering you, I wonder?

Andywiz

TOTALLY HELPING

I'm lost so even bad information is good information, cyz its something new to try but your info isn't bad, so please continue on

 

[Superstar31]

I'm not sure I understand.

Are you trying to return "9876" in the last column, but only if column E is neither a number, nor an error? Or are you trying to trap the errors that appear in column G (i.e. your #N/A, in this sample)?

At the moment the formula is ONLY evaluating the values in column E (i.e. the repeated $12.34 value). Thus, you will always get the same result on every row.

If you are trying to evaluate both the values in column E (for numerical properties) and the values in column G (for validity), then simply change the reference in the second portion of the formula, as follows:

=IF(AND(ISNUMBER(E129),NOT(ISERROR(G129))),'LCDM Revised'!H129,"")

Am I helping or hindering you, I wonder?

Andywiz

TOTALLY HELPING

I'm lost so even bad information is good information, cyz its something new to try but your info isn't bad, so please continue on

EXCELLENT, i think that last one did it now I just need to understand what you wrote

Helping or Hendering LOL you guys totally helped me out

 

[Andywiz]

OK, the trick is in the order that you place your Logical functions in the formula - Logical meaning the AND, NOT, OR type of functions.

Also, generally you shouldn't be using mathematical elements (such as *, +, -) instead of those logical functions - reserve those for actually adding, subtracting or multiplying numbers and figures. [although there are instances when you can use them.... it gets complicated!].

The formula is basically doing exactly what you described in your first posted question. To understand how to contruct these logical functions properly, you should review the explanations of the LOGICAL FUNCTIONS in the Excel help file - they're pretty good as help files go.

happy to have helped some,
Andywiz

 

[Superstar31]

OK, the trick is in the order that you place your Logical functions in the formula - Logical meaning the AND, NOT, OR type of functions.

Also, generally you shouldn't be using mathematical elements (such as *, +, -) instead of those logical functions - reserve those for actually adding, subtracting or multiplying numbers and figures. [although there are instances when you can use them.... it gets complicated!].

The formula is basically doing exactly what you described in your first posted question. To understand how to contruct these logical functions properly, you should review the explanations of the LOGICAL FUNCTIONS in the Excel help file - they're pretty good as help files go.

happy to have helped some,
Andywiz

oh my gosh, i'm soo upsset, I was soooooooo close on my own i just needed that and symbol at the beginning. also my excel 2000, doens't have a good help file, I can't even find logical fuctions when search it. but my excel at home does a better job.

So if i understand you right, use " , " to combine functions not " + " unless I actually want to add their sums up?

sweet, i'm sure i'll be back for more questions

 

[Andywiz]

Actually, what I meant was:

Instead of writing
=IF(ISNUMBER(E129)*NOT(ISERROR(E129)).....

You should write
=IF(AND(ISNUMBER(E129),NOT(ISERROR(E129)).....

Anyhow, you're correct, you did get very close on your own

Cheers,
Andy

 

[fairwinds]

I still does not understand why ISERROR is needed.
If ISNUMBER returns TRUE it is not an error.

 

[Superstar31]

The reason iserror needed is because......

Each cell is refrencing another location. So just because there is a number is cell e129 doesn't mean I want the data in cell j129.

Let me explain.....

Some employees have numbers listed in my systems, and others dont' those employees that aren't listed but who also charged back a item are showing up since there would be a number listed in e129, however using vlookup those employees aren't listed on that refrence sheet and thus give us a n/a

to make things more intriging, i have to add up the totals for stores and i dont want the total from people who don't belong to that store, but chargeback in that store number (the database that I draw the information isn't updated to reflect those people not being at that store)


Does that makes sense?

 

[fairwinds]

That would have been useful information to share from the beginning.

 

[Aladin Akyurek]

Actually, what I meant was:

Instead of writing
=IF(ISNUMBER(E129)*NOT(ISERROR(E129)).....

You should write
=IF(AND(ISNUMBER(E129),NOT(ISERROR(E129)).....

Anyhow, you're correct, you did get very close on your own

Cheers,
Andy

If the test predicates must be applied to the same cell E129, Fairwinds's reply is more than adequate compared with:

=IF(AND(ISNUMBER(E129),NOT(ISERROR(E129))...

If we must go with the clarification the OP provided later on...

"OK, let me try and explain further

if there is a number in cell e129, and not an error in cell g129, then post 3703 in cell h

Further detail, cell e129 is pulling only negative numbers from another workshet
cell f129 is pulling employee numbers from another worksheet
cell g129 is cross refrencing the data from cell f129 with a list of employees. so if an employee is not on the list (nolonger working, different departartment) whatever. cell h is pulling numbers fron another worksheet"

=IF(ISNUMBER(E129*(1-ISNA(G129))),...)

will suffice.

 

[Superstar31]

Actually, what I meant was:

Instead of writing
=IF(ISNUMBER(E129)*NOT(ISERROR(E129)).....

You should write
=IF(AND(ISNUMBER(E129),NOT(ISERROR(E129)).....

Anyhow, you're correct, you did get very close on your own

Cheers,
Andy

If the test predicates must be applied to the same cell E129, Fairwinds's reply is more than adequate compared with:

=IF(AND(ISNUMBER(E129),NOT(ISERROR(E129))...

If we must go with the clarification the OP provided later on...

"OK, let me try and explain further

if there is a number in cell e129, and not an error in cell g129, then post 3703 in cell h

Further detail, cell e129 is pulling only negative numbers from another workshet
cell f129 is pulling employee numbers from another worksheet
cell g129 is cross refrencing the data from cell f129 with a list of employees. so if an employee is not on the list (nolonger working, different departartment) whatever. cell h is pulling numbers fron another worksheet"

=IF(ISNUMBER(E129*(1-ISNA(G129))),...)

will suffice.

Please explain what the (1-isna) means? also I will try your formula and see what comes up

alas I get a #n/a where it should be blank now

but its ok, the previous attempt worked