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.

 

[Tiglet]

Tell me exactly what you want your formula to do and I'll write it

eg.

If Step 1'!B7>10 creates an error then do a calc otherwise do this calc.

Tiglet

Living on Earth is expensive, but it does include a free trip around the sun every year

 

[Excelerate2004]

Hi there, I hope I can explain this well enough...here goes:

IF 'Step 1'!B7>10,

Then'Step 1'!D10*ROUNDUP(MAX(M15:M18), 2)

ELSEIF 'Step 1'!B7<=10,

THEN ROUNDUP(MAX(M15:M18), 2)

ENDIF

Simple enough right?

In addition to that I need an ISERROR in there to cover off both ROUNDUP calculations so that if there is an error it will display a "NA". I've been trying to do a nested IF, but cant seem to get it right.

I hope this further elaborates what I'm trying to do.

Thanks for your help, I greatly appreciate it.

Cheers!

 

[xlbo]

This should do the trick - you have too many IFs in your 2nd statement BTW:

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

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[SkipVought]

Excelerate2004,

Tell me what would cause ROUNDUP(MAX(M15:M18), 2) to error???

ONLY if a value in M15:M18 returned an error.

THAT is where you need to trap out your errors UNLESS you are lloking for the NA as an indicator of a problem elsewhere, in which case an NA in M15:M18 would get you to the problem quicker.

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Excelerate2004]

To Skipvought:

This is a multi sheet calculation whereby an NA is applied to the cells in the range M15:M18 based on user input from another sheet. If and only if there is no values applicable from another user input table then the NA's get brought in to a AVERAGE calculation whereby the #DIV/0 is created.

I was looking for an easier way to get rid of the #DIV/0 error rather than having to code it for each individual cell. So I was catching it at a later point, in a summary table.

But I think I will eventually have to go back & code and remove that #DIV/0 error altogether.

This may be a fix for now.

Thanks to everyone for all your help!

 

[xlbo]

Did you not try my formula ??

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[Excelerate2004]

To Xlbo:

Worked perfectly! Thank you so much!

I think I will have to go back to a previous table that "feeds" the table that I used your ISERROR statement on and somehow apply the ISERROR code trap there however.

But for now your code works great!

Thanks again!

 

[Excelerate2004]

Hello all, Same question different lines of code.

I'm trying to adapt and incorporate xlbo's code so that I can catch my #DIV/0! error before it gets to the point of the code posted above.

=IF('Step 1'!B6<=2,SUM('Step 2'!E11:E12)/COUNTIF('Step 2'!E11:E12, "<>NA"),
IF('Step 1'!B6<=5,SUM('Step 2'!F11:F12)/COUNTIF('Step 2'!F11:F12, "<>NA"),
IF('Step 1'!B6<=10,SUM('Step 2'!G11:G12)/COUNTIF('Step 2'!G11:G12, "<>NA"))))

Again if possible I'd like to use the ISERROR function to catch any errors from the SUM calculation on each line.

Is there any easy way to write this code, I seem to be having trouble with the brackets and too many arguments.

Thanks for any help I can get!

 

[vaneagle]

Hi Excelerate2004,

Instead of trying to trap the errors with if statements, why not replace you sum formula's with the following example:

=SUMIF('Step 2'!E11:E12,">-1e9") and so on..

This will sum the following:

Col A
1
5
#DIV/0!
6
text
#REF!
=SUMIF(A1:A6,">-1e9") 
Answer = 12

You can keep your current if statements and just replace the sum with the sumif!

Hope this helps...

vaneagle

 

[Excelerate2004]

Hey vaneagle,

What exactly does -1e9 do?

I dont think I can use your suggestion as I use the "<> NA" as a way of excluding cells that dont have values in them in an AVERAGE function calculation.

Thanks for the try though

 

[KenWright]

Just a thought, but assuming that the formula you posted, ie

=IF('Step 1'!B6<=2,SUM('Step 2'!E11:E12)/COUNTIF('Step 2'!E11:E12, "<>NA"),
IF('Step 1'!B6<=5,SUM('Step 2'!F11:F12)/COUNTIF('Step 2'!F11:F12, "<>NA"),
IF('Step 1'!B6<=10,SUM('Step 2'!G11:G12)/COUNTIF('Step 2'!G11:G12, "<>NA"))))

can be replaced with:-

=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")

then maybe this will cover with the error checking included:-

=IF(ISERROR(My_Formula),0,My_Formula)

The formula starts at 'Step 1'!B6 and then uses match to get a value of 0,1,2 with which it offsets that many columns from 'Step 1'!B6, and then uses the height argument to determine the range you are evaluating (ie 2 cells in this case), performs the COUNTIF on that range, SUMs it and does the average

ie

=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")),0,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"))

The -0.0001 was me trying to stop it causing an error by you having a 0 value in 'Step 1'!B6, so not sure if it really can be 0 or even negative.

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

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

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

 

[Excelerate2004]

Hello Ken,

I tried using your suggestion (not totally understanding it, very much beyond what I know at this point) and recieved a 0 where I should have gotten a NA.

So I swapped out the 0 in the code for an NA and it gave me the value I was looking for. Not sure if this is totally correct?

The true test is, if its possible, could you fit this code (see below) into a solution similar to your previous posted suggestion, with the ISERROR covering off the calculation on each line.

=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"))))


If I can test that, then I'll know that the previous solution works.

Either way, I owe you a huge thanks for taking time to help with this.


Cheers!

 

[vaneagle]

As i understand it the -1e9 acts to ignore anything that is not numerical.

Sorry.. I have misunderstood... the SUM('Step 2'!C1315) is not the problem its the countif...

 

[KenWright]

OK, but will have to look at it when I get home from work, but, what you have said is what I would have expected. In the formula you posted you didn't cater for what happens if an error is returned by the formula, so I just put a 0 in there, ie

=IF(ISERROR(My_Formula),0,My_Formula)

If you want NA returned if the formula returns an error then you woudl do exactly as you have done and change it from:-

=IF(ISERROR(My_Formula),0,My_Formula) to
=IF(ISERROR(My_Formula),"NA",My_Formula)

The one advantage that my formula will have over the nested ifs is that it will scale better, in that if you have more break points to add, ie 2,5,10,15,20 etc, then it is just a case of adding those values to the formula and changing the

(3-MATCH('Step 1'!B6,{10,5,2,-0.0001},-1)) bit to

(5-MATCH('Step 1'!B6,{20,15,10,5,2,-0.0001},-1)) and so on.
^ ^ ^

For the values you have though, there is probably not much difference to the nested If structure, so I just threw it in as an alternative option.

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



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

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

 

[Excelerate2004]

Once again thanks for your time,

The future user of this project tells me that those three breakpoints of 2 5 and 10 will be as far as it goes. Those 3 breakpoints are based on a framework that is very much set in stone.

But your code is very efficient and like you said IF I had to change the break points at a later date then it could be easily done.

Now if I can just get past that previously posted problem then I'll be good to go.

Cheers

 

[KenWright]

OK, just to confirm though, the ranges have changed and are now 2 dimensional vs 1 dimensional as before yes?

eg before it was

E11:E12
F11:F12
G11:G12

and now it is

C1315
C13:E15
C13:F15

This involves just a slight tweak on the formula I gave you previously, to make what was the height argument become the width argument, putting in a hardcoded height argument as all your ranges are 3 cells high and changing the 3 to a 5, so that the 3 possible values it will return to the width argument will be 2,3,4 ie C (2) or C:E (3) or C:F (4) columns wide

This gives

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

which when expanded using the previous logic of

=IF(ISERROR(My_Formula),"NA",My_Formula)

becomes:-

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

Give that a go

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

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

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

 

[Excelerate2004]

Hello again,

I won't be able to test it for a few hours, but hopefully that will do the trick. I will post for all to see either way in a short while.

Cheers!

 

[Excelerate2004]

Hi Ken,

I tested the code on an earlier version and everything seems great!!

I'm still alittle worried as I dont completely grasp entirely how your code works...so if I ran into trouble I'm not sure I could tweak it to fix the problem.

But you are right I moved from 1 dimension to 2 dimensions, I think your code will cover off everything I needed such as going from the ranges C1112 and C1315 then to C1619. Plus the error checking if its required.

I wont totally know if the code works until I do some more thorough testing.

But as it stands now it seems awesome!

I'm very thankful for your time and energy!!

Once again a huge THANKS!

Cheers

 

[KenWright]

Can't have that, so lets see if we can walk through it for you:-

=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")

Lets first take a look at OFFSET, as this whole formula revolves round this function.

=offset(reference,rows,cols,[height],[width])

If E11 is the start reference, then OFFSET allows you to say how many rows/columns to move from that reference, and then bring back that cell/range, eg

=offset(E11,2,4) = cell I13
=offset(E11,3,4) = cell I14
=offset(E11,4,4) = cell I15
=offset(E11,2,5) = cell J13
=offset(E11,2,6) = cell K13
=offset(E11,2,7) = cell L13
and so on.

If you use the last two optional arguments, either singly or jointly, you can create a range of cells from this formula, eg:-

=offset(E11,2,4,5,0) = range I13:I17 1D
=offset(E11,2,4,6,0) = range I13:I18 1D
=offset(E11,4,4,0,5) = range I15:M15 1D
=offset(E11,4,4,0,6) = range I15:N15 1D
=offset(E11,2,6,5,9) = range K13:S17 2D
and so on.

(The last two arguments are optional and can be left out, or you can just put a comma in if you need to specify just the width argument, eg =offset(E11,2,3,,4) - I left the 0s in for clarity)

Any time you see ranges like the ones you posted:-

E11:E12
F11:F12
G11:G12

Note the pattern - each 2 cells high, moves away from E11 0,1,2 columns each time

You just know that you can achieve this using Offset, as the range in the first group starts at E11, is 2 cells high and you either move across 0 columns, 1 columns, 2 columns to get what you need, so as long as you use Offset, and can feed it the 0,1,2 into the columns argument by use of some function, you know you can build that reference. The way I chose to build that reference was via the MATCH function:-

Again, first thing to do is break it down. The one common bit to both elements of that formula is the bit

(3-MATCH('Step 1'!B6,{10,5,2,-0.0001},-1))

The MATCH..... bit, takes whatever is in cell B6 and tries to match it against the values in the array {10,5,2,-0.0001}. If it finds a match, eg 5, then 5 is the second value in the array and it returns a 2. If it doesn't find a match then the last argument of -1 at the end means that it will find the smallest value that is greater than or equal to the lookup_value ie B6. The Lookup_array must be placed in descending order: TRUE, FALSE, Z-A, ...2, 1, 0, -1, -2, ..., and so on. This means that if B6 had a 3 in it, it won't find a match so will default to the smallest value that is greater than 3, ie 5, and again in this instance it will return a 2.

This means that you can potentially get 3,2,1, BUT, I really want 0,1,2 so

The 3-Match_Value means you will get either:-

3-3 = 0
3-2 = 1
3-1 = 2

The very values you need to feed to the Offset function to tell it which range to look at.

And that's pretty much it. The rest was your formula anyway, so all I did was help it to look for the right ranges, based on what was in B6.

Second was almost identical just with different ranges, but the principle was the same.

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

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

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