Help combining ISERROR and IF statements

[Excel-erate2004]

Hello all,

Just wondering if any one would have a quick fix solution, I've bee
looking at this for what seems hours??

I want to combine these 2 lines into one, but can't seem to do it.

=IF(ISERROR(ROUNDUP(MAX(M15:M18), 2)), "NA", (ROUNDUP(MAX(M15:M18)
2)))


=IF('Step 1'!B7>10,'Step 1'!D10*ROUNDUP(MAX(M15:M18), 2), IF('Ste
1'!B7<=10,ROUNDUP(MAX(M15:M18), 2)))


Basically I want the ISERROR to cover off the parameters B7>10 an
B7<=10 and their resultant calculations. So it might look somethin
like this:


=IF(ISERROR(IF('Step 1'!B7>10,'Step 1'!D10*ROUNDUP(MAX(N15:N18), 2)
IF('Step 1'!B7<=10,ROUNDUP(MAX(N15:N18), 2)), "NA", ('Ste
1'!B7>10,'Step 1'!D10*ROUNDUP(MAX(N15:N18), 2), IF('Ste
1'!B7<=10,ROUNDUP(MAX(N15:N18), 2))))



Thanks for any help I can get! This seems very tricky!

P.S. Both these lines work independently I just want to be able t
combine them into one

 

[Frank Kabel]

Hi
not a nice solution but try
=IF(ISERROR(IF('Step 1'!B7>10,'Step 1'!D10*ROUNDUP(MAX(M15:M18), 2),
IF('Step1'!B7<=10,ROUNDUP(MAX(M15:M18), 2)))),"NA",IF('Step
1'!B7>10,'Step 1'!D10*ROUNDUP(MAX(M15:M18), 2),
IF('Step1'!B7<=10,ROUNDUP(MAX(M15:M18), 2))))

Better would be to prevent the error in using only valid values in your
range

--
Regards
Frank Kabel
Frankfurt, Germany

"Excel-erate2004 >" <<[email protected]>
schrieb im Newsbeitrag
news:[email protected]...

 

[Excel-erate2004]

I agree with you, I need to catch the error in my range, if possibl
could you suggest how I could place an ISERROR function on eac
calculation for the code posted below:

=IF('Step 1'!B6<=2,SUM('Step 2'!C1315)/COUNTIF('Step 2'!C1315
"<>NA"),
IF('Step 1'!B6<=5,SUM('Step 2'!C13:E15)/COUNTIF('Step 2'!C13:E15
"<>NA"),
IF('Step 1'!B6<=10,SUM('Step 2'!C13:F15)/COUNTIF('Step 2'!C13:F15
"<>NA"))))


I get too messed up with the brackets and too many IF arguments.

Any help I can get would be great,

Thanks a bunch!

 

[Frank Kabel]

Hi
could it happen in this calculation that the COUNTIF part returns zero?
or do you have text values in C1315?

So something like the following may help:

=IF(AND('Step 1'!B6<=2,COUNTIF('Step 2'!C1315"<>NA")>0),SUM('Step
2'!C1315)/COUNTIF('Step 2'!C1315,
"<>NA"),IF(AND('Step 1'!B6<=5,COUNTIF('Step
2'!C13:E15"<>NA")>0),SUM('Step 2'!C13:E15)/COUNTIF('Step 2'!C13:E15,
"<>NA"),IF('Step 1'!B6<=10,COUNTIF('Step 2'!C13:F15"<>NA")>0),SUM('Step
2'!C13:F15)/COUNTIF('Step 2'!C13:F15,
"<>NA"))))





--
Regards
Frank Kabel
Frankfurt, Germany

"Excel-erate2004 >" <<[email protected]>
schrieb im Newsbeitrag
news:[email protected]...

 

[Excel-erate2004]

Hi Frank!

I could potentially have text values in C13:C15...

How it works is that I get the user to enter an NA if a value of a cel
is not applicable in a previous table. This previous table is used a
the basis for this average calculation:

SUM('Step 2'!C1315)/COUNTIF('Step 2'!C1315,

"<>NA"),

It sums the values from C13 to C15 and divides them by the number o
cells, giving me the average, as long as the cells value is not NA.

I get a #DIV/0! error if I get NA's in the cells that the range i
covering.

So I need to trap that error to avoid getting that nasty looking erro
message.

I hope this helps explain my problem. I'm very much stuck on thi
part...

Thanks for your help

 

[beeawwb]

What about...

=IF((COUNTIF(C1315,"<>NA")<>0),(SUM(C1315)/COUNTIF(C1315,"<>NA")),"")

?

Hope it helps

-Bo