# Help with Isnumber * iserror

#### Superstar31

##### Active Member
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

### Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

#### Andywiz

##### Board Regular
Try:

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

Hope this helps some.
Andywiz

#### fairwinds

##### MrExcel MVP
Welcome to the board!

As far as I can understand

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

should be enough.

#### Andywiz

##### Board Regular
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

##### Board Regular
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

##### Active Member
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

##### Board Regular
superstar,

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

#### Superstar31

##### Active Member
fairwinds said:
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

##### Board Regular
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

##### Active Member
Andywiz said:
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

Replies
3
Views
379
Replies
5
Views
307
Replies
11
Views
256
Replies
1
Views
172
Replies
3
Views
193

1,195,858
Messages
6,011,979
Members
441,661
Latest member
Pammie007

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back