Combining IF statements

[Excel-erate2004]

Hello all,

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

=IF('Step 1'!B7>10,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

 

[Frank Kabel]

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'!B7>10,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

yo

 

[Excel-erate2004]

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'!B7>10,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

 

[Frank Kabel]

Hi
try the following formula
=IF(COUNTIF('step1'!L15:L18,"NA")>0,"NA",IF(ISERROR(ROUNDUP(MAX(L15:L18
),2)*(1+'step1'!D10*('step1'!B7>10))),"NA",ROUNDUP(MAX(L15:L18),2)*(1+'
step1'!D10*('step1'!B7>10))))


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

 

[Excel-erate2004]

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!

 

[Excel-erate2004]

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???

 

[Frank Kabel]

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

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

 

[Excel-erate2004]

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

 

[Frank Kabel]

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

 

[Excel-erate2004]

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

 

[Frank Kabel]

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