Excel: Changing #NUM! error to zero.

  Peverelli 11:20 26 Sep 03
Locked

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 03

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 03

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 03

the ISERROR function...

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

Does that make sense?

Regards,

Simsy

  Peverelli 00:37 27 Sep 03

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 03

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 03

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

  Simsy 10:04 27 Sep 03

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!

Regards,

Simsy

  Peverelli 10:40 27 Sep 03

"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 03

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 03

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.

Best phone camera 2016/2017: Galaxy S7 vs iPhone 7 vs Google Pixel vs HTC 10 Evo vs OnePlus 3T vs…

1995-2015: How technology has changed the world in 20 years

Best Christmas Agency Projects of 2016

Super Mario Run preview | Hands-on first impressions of Super Mario Run: Mario's iPhone & iPad…