IFERROR (or is it IF(AND) still giving #VALUE!

[mjacobse]

I seem lost on this one. I am trying to combine a number of simple functions and seem to be going in circles of failure.

I'm working towards placing a result in Column I based on a few things:
I never know just how many rows of data there will be so I check for any content in column A, if A has no content, put nothing "" in I for that row, otherwise move on with other checks.

I also know that column H is supposed to have values between 1 and 1,000,000 (as text) and could also have the text "(blank)"

Any other content in H (alphanumerics, pure text or "values" (as text) not within the range of 1 to 1,000,000) should then result in "Requisition Number not to standard"

I've tried reordering the functions and played with shifting closing parentheses but I either get a result of #Value ! or "too many arguments" - specifically when there are instances of non numeric content in column I (the text formatted "value" of the requisition number).


This seems to work fine:
=IF(A5="","",IF(H5="(blank)","--",IFERROR(VALUE(H5),"Requisition Number not to standard")))

But I also need the below (or similar) included in the function group so that the good values between 1 and 1,000,000 are converted to genuine values.

IF(AND(VALUE(H5)>0,VALUE(H5)<1000000),VALUE(H5)

so that it would be something like this.

=IF(A5="","",IF(H5="(blank)","--",IFERROR(VALUE(H5),"Requisition Number not to standard",IF(AND(VALUE(H5)>0,VALUE(H5)<1000000),VALUE(H5))))

Again, I've tried leading and ending with the IFERROR (as well as the IF(AND) and embedding each midway.

What the heck am I doing wrong or what are alternates?


Any and all help is greatly appreciated.


Thanks.

 

[Eric W]

Welcome to MrExcel!

How about:

Book1
	H	I
4	500	500
5	123,456	123456
6	(Blank)	--
7	abcd	Requisition Number not to standard
8	-999	Requisition Number not to standard
9	5,000,000	Requisition Number not to standard
10	123456	123456
11	help	Requisition Number not to standard
12	44	44
13		Requisition Number not to standard
Sheet3

Cell Formulas
Range	Formula
I4	=IF(H4="(Blank)","--",IFERROR(1/(1/IF(AND(H4+0>0,H4+0<1000000),H4+0,0)),"Requisition Number not to standard"))

 

[Eric W]

Perhaps a bit simpler:

=IF(H4="(Blank)","--",IFERROR(IF(AND(H4+0>0,H4+0<1000000),H4+0,NA()),"Requisition Number not to standard"))