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

 

[Andywiz]

Try:

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

Hope this helps some.
Andywiz

 

[fairwinds]

Welcome to the board!

As far as I can understand

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

should be enough.

 

[Andywiz]

And in answer to your second question, just hit F1, click the INDEX tab, and type in FUNCTION.

This should provide you with categorised lists of functions - the logical ones would be a good place to start for things like this.

Andywiz

 

[Andywiz]

And in answer to your second question, just hit F1, click the INDEX tab, and type in FUNCTION.

This should provide you with categorised lists of functions - the logical ones would be a good place to start for things like this.

Andywiz

 

[Superstar31]

Try:

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

Hope this helps some.
Andywiz

CP ($12.34) xxxxxxxx Namewithheld 9876
LF ($12.34) xxxxxxxx #N/A 9876
92 ($12.34) xxxxxxxx namewithheld 9876

.......

It still produces the 9876 with your command next to the #n/a field

 

[Andywiz]

superstar,

Which cell from this sample are you trying to test against your criteria?

 

[Superstar31]

Welcome to the board!

As far as I can understand

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

should be enough.

sorry i dont think my post really gave enought info

d e f g
129 CP ($12.34) xxxxxxxx Namewithheld 9876
130 LF ($12.34) xxxxxxxx #N/A 9876
131 92 ($12.34) xxxxxxxx namewithheld 9876

Hopeully this is enough data

 

[Andywiz]

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

 

[Superstar31]

superstar,

Which cell from this sample are you trying to test against your criteria?

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