Help combining ISERROR with IF statement 3

[Excelerate2004]

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

I want to combine these 2 lines into one, but cant 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('Step 1'!B7<=10,ROUNDUP(MAX(M15:M18), 2)))


Basically I want the ISERROR to cover off the parameters B7>10 and B7<=10 and their resultant calculations. So it might look something 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", ('Step 1'!B7>10,'Step 1'!D10*ROUNDUP(MAX(N15:N18), 2), IF('Step 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 to combine them into one.

 

[Excelerate2004]

Wow!

Thats incredibly efficient code, I do have a better understanding of what the code actually does. I would never have been able to come up with that. Thats amazing! How long have you been programming in Excel?

I'll be sure to archive your explanation just in case I cant find this thread again.

Thanks man, much appreciated!

 

[KenWright]

A few years, but only really got to grips with some of the real power of it in the last 2-3 years.

And you're welcome

Best Wishes
Ken..................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[Excelerate2004]

Arggh! this is not over for me yet! I know, I know i'm pushing it, but I have a glitch that I can't figure out.

I'm trying to adapt my code (see below) into the latest suggestion.

=IF('Step 1'!B6<=2,SUM('Step 2'!E32:E35,'Step 2'!C36:E36)/COUNTIF('Step 2'!E32:E36, "<>NA"),
IF('Step 1'!B6<=5,SUM('Step 2'!F35:F35,'Step 2'!C36:F36)/COUNTIF('Step 2'!F32:F36, "<>NA"),
IF('Step 1'!B6<=10,SUM('Step 2'!G32:G35,'Step 2'!C36:G36)/COUNTIF('Step 2'!G32:G36, "<>NA"))))



Using the posted suggestion and adapting my code, gives me this:

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

My problem comes with the fact that the last block of cells (from which part of this calculation is based on) is merged, the range of these merged cells is C36:E36.

The above code works, but is inconsistent.

For example if I have a 0.33 in the range from E32:E35 and
a 0.00 in the range C36:E36, I get back 0.17 which is correct.

BUT if those numbers are reversed whereby the 0.33 is amongst the range of the merged cells C36:E36 and the 0.00 is amongst this range E32:E35, my calculation

SUM('Step 2'!E32:E35,'Step 2'!C36:E36)/COUNTIF('Step2'E32:E36, "<>NA")

doesnt work. So obviously there is a problem with those merged cells fitting into these calculations.

Not sure if anyone is still following me, but basically what I'd like to be able to do is have a 1D and a 2D combo formula, that combines the 1D array with the 2D array.

Any ideas?

 

[KenWright]

I avoid merged cells *like the plague* for reasons just such as these. I have never yet come across a situation that really required them, and where ther desired effect couldn't be achieved by using Format cells / alignment / horizontal / 'centre across selection'

I would *never* place any reliance on doing what you are doing on ranges that involve merged cells without an absolute abundance of testing. It's just not worth the effort.

I honestly don't know anybody in any of the Excel groups/forums that has any different opinion wrt merged cells. You are simply asking for trouble if you keep them, *especially* if you plan to use those ranges inside any calculation formulas.

Regards
Ken.................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[Excelerate2004]

Thanks for the advice, you learn something new everyday!

I'll go back to the user and suggest a workaround.

As always, your help is much appreciated!

Hopefully someone else will get some indirect help from all these posts.

Cheers!