Excel: Changing #NUM! error to zero.

  Peverelli 11:20 26 Sep 2003

I'm sure I've managed to do this before but...

In cell B9 I have the formula "=COMBIN(A9,B5)*COMBIN(C2-A9,6-B5)". This works fine except for when (C2-A9) is less than (6-B5), [e.g. COMBIN(2,3)] - which gives the 'result' #NUM!.

What I want to do is to force Excel to place a zero in cell B9 when it encounters this error.

Steel capped boots are ready for me to kick myself with :O)

  Inky 12:28 26 Sep 2003

You could use an IF statement that checks if(C2-A9) is more than (6-B5). If it isn't you could then return some other value?

  Peverelli 13:26 26 Sep 2003

Boot "OW" - Kick "OW".

So simple, why didn't I think of that? Now I've got "=IF(C2-A9<6-B5,0,COMBIN(A9,B5)*COMBIN(C2-A9,6-B5))"

Thanks Inky.

  Simsy 16:38 26 Sep 2003

the ISERROR function...

eg... =if(ISERROR("your test here"),0,your test)

Does that make sense?



  Peverelli 00:37 27 Sep 2003

Thanks. Yes I understood and it works, although in this case the other way means less typing. Two ways of solving the problem is better than none.

  VoG II 00:43 27 Sep 2003

Both will work. The IF by Inky is better since the COMBIN is done only once, hence quicker. Probably not noticeable on a fast 'puter. Sorry Simsy.

  Peverelli 00:45 27 Sep 2003

Hi VoG. That's better, an Excel thread isn't complete until your stamp is on it ;O)

  Simsy 10:04 27 Sep 2003

I didn't even know there was a "COMBIN" fuction..... now there's something else I've got to learn about!

That's what I love about this site.. just browsing and I'm learning al the time!



  Peverelli 10:40 27 Sep 2003

"COMBIN" is useful for when you want to (for example)quickly calculate how many lines your entry needs when doing the football pools. So if you have selected 12 draws and want to "perm" any 8 from 12, the formula is "=COMBIN(12,8)" which gives you 495. The pools companies and the bookmakers erroneously call this a permutation. Try "=PERMUT(12,8)" and you'll find this to be a much higher figure.

Agree with you 100% about learning through browsing this site.

  VoG II 10:48 27 Sep 2003

Yep, definitely learn all sorts on this site.

To learn about "obscure" Excel functions you cannot get much better than the Excel function dictionary click here

There will be a test tomorrow!

  Peverelli 11:18 27 Sep 2003

Cheers VoG. I was planning to work on some new songs over the next few hours and now I've got homework to do instead. ;O)

Good link, looks very interesting.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

HTC U12 Plus review: Hands-on

Brilliant (and Weird) World Cup 2018 Art and Design Projects

Best Android emulators for Mac

TV & Streaming : comment regarder Roland Garros ?