Combining IF statements
 

Hello all,

I've been unable to incorporate these two IF statements into one,

=IF('Step 1'!B710,IF(ISERROR(ROUNDUP(MAX(L15:L18), 2)), "NA"
ROUNDUP(MAX(L15:L18), 2)*'Step 1'!D10),
IF(('Step 4'!L15 = "NA"), "NA",), IF(ISERROR(ROUNDUP(MAX(L15:L18), 2))
"NA", ROUNDUP(MAX(L15:L18), 2)))

and


=IF(('Step 4'!L15 = "NA"), "NA",)

Is there anybody out there who can help me bring these two statement
into one. They each work individually, but I can't get them together.
I continue to have trouble with the parenthesis.

If possible I'd like to also include a range of cells instead of jus
1. For example, instead of L15 is it possible to use something like:

=IF(('Step 4'!L15:L18 = "NA"), "NA",)

Thanks for any help I can ge

--
Message posted from http://www.ExcelForum.com

Combining IF statements
 

Hi
you may explain in detail what you're trying to achieve. Describe in
plain text your desired logic (e.g. for your check of the range againt
'NA')

--
Regards
Frank Kabel
Frankfurt, Germany


Hello all,

I've been unable to incorporate these two IF statements into one,

=IF('Step 1'!B710,IF(ISERROR(ROUNDUP(MAX(L15:L18), 2)), "NA",
ROUNDUP(MAX(L15:L18), 2)*'Step 1'!D10),
IF(('Step 4'!L15 = "NA"), "NA",), IF(ISERROR(ROUNDUP(MAX(L15:L18),
2)), "NA", ROUNDUP(MAX(L15:L18), 2)))

and


=IF(('Step 4'!L15 = "NA"), "NA",)

Is there anybody out there who can help me bring these two statements
into one. They each work individually, but I can't get them
together.
I continue to have trouble with the parenthesis.

If possible I'd like to also include a range of cells instead of just
1. For example, instead of L15 is it possible to use something like:

=IF(('Step 4'!L15:L18 = "NA"), "NA",)

Thanks for any help I can get


---
Message posted from http://www.ExcelForum.com/Hi
yo

Combining IF statements
 

Here is my Pseudo-code, in plain logic:

If on sheet (Step1) at Cell B7 is greater than 10 then
get the max value in the range L15:L18. Roundup this max value in thi
range. Then multiply this value by the value in Cell D10 on sheet Ste
1. Catch any errors by placing an NA.

If on sheet (Step1) at Cell B7 is less than 10 then get the max valu
in the range L15:L18. Roundup this max value in this range.
But do not multiply by the value in Cell D10.

As well, If any of the cells in the range from L15 to L18 contain a
"NA" enter an NA in the cell that this formula is placed. In this cas
J145.

I made a mistake in posting my code, it shouldve been integrate th
following:

=IF('Step 1'!B710,IF(ISERROR(ROUNDUP(MAX(L15:L18), 2)), "NA"
ROUNDUP(MAX(L15:L18), 2)*'Step 1'!D10),
IF(ISERROR(ROUNDUP(MAX(L15:L18), 2)), "NA", ROUNDUP(MAX(L15:L18), 2)))

AND

=IF(('Step 4'!L15 = "NA"), "NA")

Hope this clarifies things

Thanks a bunch

--
Message posted from http://www.ExcelForum.com

Combining IF statements
 

Hi
try the following formula
=IF(COUNTIF('step1'!L15:L18,"NA")0,"NA",IF(ISERRO R(ROUNDUP(MAX(L15:L18
),2)*(1+'step1'!D10*('step1'!B710))),"NA",ROUNDUP (MAX(L15:L18),2)*(1+'
step1'!D10*('step1'!B710))))


Notes:
- I assumed that if B7=10 you also didn't want a multiplication with
cell D10 (you missed this condition in your pseudo code).
- Instead of this general ISERROR check I would restrict this to the
cells which really could contain an error. As I don't know in which
cases you could have an error in any of the referenced cells no better
solution possible
- also not sure why you used a sheet named 'step4' in your newest
example

--
Regards
Frank Kabel
Frankfurt, Germany


Here is my Pseudo-code, in plain logic:

If on sheet (Step1) at Cell B7 is greater than 10 then
get the max value in the range L15:L18. Roundup this max value in
this range. Then multiply this value by the value in Cell D10 on
sheet Step
1. Catch any errors by placing an NA.

If on sheet (Step1) at Cell B7 is less than 10 then get the max value
in the range L15:L18. Roundup this max value in this range.
But do not multiply by the value in Cell D10.

As well, If any of the cells in the range from L15 to L18 contain an
"NA" enter an NA in the cell that this formula is placed. In this
case J145.

I made a mistake in posting my code, it shouldve been integrate the
following:

=IF('Step 1'!B710,IF(ISERROR(ROUNDUP(MAX(L15:L18), 2)), "NA",
ROUNDUP(MAX(L15:L18), 2)*'Step 1'!D10),
IF(ISERROR(ROUNDUP(MAX(L15:L18), 2)), "NA", ROUNDUP(MAX(L15:L18),
2)))

AND

=IF(('Step 4'!L15 = "NA"), "NA")

Hope this clarifies things

Thanks a bunch!


---
Message posted from http://www.ExcelForum.com/

Combining IF statements
 

Thanks!

It works great, I'll have to test it completly before implementing but
so far so good!

I'll take your notes into consideration as well.

Cheers!


---
Message posted from http://www.ExcelForum.com/

Combining IF statements
 

I've tested the code and it works fine for the range of cells (i.e
L15:L18 = "NA") that have an NA, however when the range of cells do not
have an NA the cell still yields an NA as an end result. Thus its not
completly correct.

Any suggestions???


---
Message posted from http://www.ExcelForum.com/

Combining IF statements
 

Hi
in this case the ISERROR function seems to issue this warning. Try
=IF(COUNTIF('step1'!L15:L18,"NA")0,"NA",IF(ISERRO R(ROUNDUP(MAX(L15:L18
),2)*(1+'step1'!D10*('step1'!B710))),"ISERROR
error",ROUNDUP(MAX(L15:L18),2)*(1+'
step1'!D10*('step1'!B710))))

what do you receive now? I assume 'ISERROR error'. If yes you may have
text values or other errors in your used cells L15:L18, D10



--
Regards
Frank Kabel
Frankfurt, Germany


I've tested the code and it works fine for the range of cells (i.e
L15:L18 = "NA") that have an NA, however when the range of cells do
not have an NA the cell still yields an NA as an end result. Thus
its not completly correct.

Any suggestions???


---
Message posted from http://www.ExcelForum.com/

Combining IF statements
 

I did get the "ISERROR error" in my cell after using your most recen
suggestion.

The thing is what to do next?

I use the "NA" as a placeholder to indicate to the user that this cell
value is not available. Also I have code that says that if the cell'
value is NA do not include in any calculations or in otherwords COUNTI
< "NA".

Here is the code for L15 maybe this might help or confuse even mor
lol

=IF(ISERROR(SUM(OFFSET('Step 2'!E11,,(3-MATCH('Ste
1'!B6,{10,5,2,-0.0001},-1)),2))/COUNTIF(OFFSET('Ste
2'!E11,,(3-MATCH('Ste
1'!B6,{10,5,2,-0.0001},-1)),2),"<NA")),"NA",SUM(OFFSET('Ste
2'!E11,,(3-MATCH('Ste
1'!B6,{10,5,2,-0.0001},-1)),2))/COUNTIF(OFFSET('Ste
2'!E11,,(3-MATCH('Step 1'!B6,{10,5,2,-0.0001},-1)),2),"<NA"))


Either way I'm not quite sure where to go from here.


Thanks for all your hel

--
Message posted from http://www.ExcelForum.com

Combining IF statements
 

Hi
send me your file and I'll have a look at it
email: frank[dot]kabel[at]freenet[dot]de

I assume you're multiplying/adding text values in the final formula


--
Regards
Frank Kabel
Frankfurt, Germany


I did get the "ISERROR error" in my cell after using your most recent
suggestion.

The thing is what to do next?

I use the "NA" as a placeholder to indicate to the user that this
cells value is not available. Also I have code that says that if the
cell's value is NA do not include in any calculations or in
otherwords COUNTIF < "NA".

Here is the code for L15 maybe this might help or confuse even more
lol

=IF(ISERROR(SUM(OFFSET('Step 2'!E11,,(3-MATCH('Step
1'!B6,{10,5,2,-0.0001},-1)),2))/COUNTIF(OFFSET('Step
2'!E11,,(3-MATCH('Step
1'!B6,{10,5,2,-0.0001},-1)),2),"<NA")),"NA",SUM(OFFSET('Step
2'!E11,,(3-MATCH('Step
1'!B6,{10,5,2,-0.0001},-1)),2))/COUNTIF(OFFSET('Step
2'!E11,,(3-MATCH('Step 1'!B6,{10,5,2,-0.0001},-1)),2),"<NA"))


Either way I'm not quite sure where to go from here.


Thanks for all your help


---
Message posted from http://www.ExcelForum.com/

Combining IF statements
 

The file is rather large, its about 6 MB's or 6000 K, can you accept
file of that size

--
Message posted from http://www.ExcelForum.com

Combining IF statements
 

Hi
no probelm but you may zip the file prior to sending it (and thanks for
the warning..)

--
Regards
Frank Kabel
Frankfurt, Germany


The file is rather large, its about 6 MB's or 6000 K, can you accept
a
file of that size?


---
Message posted from http://www.ExcelForum.com/