x404.co.uk
http://www.x404.co.uk/forum/

Excel help
http://www.x404.co.uk/forum/viewtopic.php?f=3&t=13743		 Page 1 of 1
Author:  l3v1ck [ Mon May 23, 2011 9:07 pm ]
Post subject:  Excel help
I'm trying to combine two formulas without success, can anyone help?

=IsNumber(B6)
=IF(C6=$B6,1,0)

What I'm wanting Excel to do is:
Is there a number in B6? If not, leave cell blank, if there is do the following equation:
If C6 is the same as B6, then the cell value should be 1, if it isn't, the cell value should be 0.

How do I get that to work? I've tried google, but I can't seem to find a way to combine the IsNumber and IF functions.
Author:  Fogmeister [ Mon May 23, 2011 9:12 pm ]
Post subject:  Re: Excel help
l3v1ck wrote:
I'm trying to combine two formulas without success, can anyone help?

=IsNumber(B6)
=IF(C6=$B6,1,0)

What I'm wanting Excel to do is:
Is there a number in B6? If not, leave cell blank, if there is do the following equation:
If C6 is the same as B6, then the cell value should be 1, if it isn't, the cell value should be 0.

How do I get that to work? I've tried google, but I can't seem to find a way to combine the IsNumber and IF functions.

Yup there is but I'm not sure I fully understand the problem.

Ah, ok... re-read.

=IF(IsNumber(B6), if(C6 = B6, 1, 0), "")

That should work.
Author:  l3v1ck [ Mon May 23, 2011 9:14 pm ]
Post subject:  Re: Excel help
Thanks
I'll give it a try.
Author:  l3v1ck [ Mon May 23, 2011 9:18 pm ]
Post subject:  Re: Excel help
Tried it with and without the spaces inbetween characters, but the result is always blank.
Author:  l3v1ck [ Mon May 23, 2011 9:21 pm ]
Post subject:  Re: Excel help
Ah. Your formula does work if the inputs are numbers. My inputs in B6 and C6 are letters.
If the letters match I want it to put a 1 as teh cell value and a 0 if the don't.
Author:  Fogmeister [ Mon May 23, 2011 9:26 pm ]
Post subject:  Re: Excel help
l3v1ck wrote:
Ah. Your formula does work if the inputs are numbers. My inputs in B6 and C6 are letters.
If the letters match I want it to put a 1 as teh cell value and a 0 if the don't.

Ah OK, this is opposite to your description though...

Quote:
Is there a number in B6? If not, leave cell blank, if there is do the following equation:
If C6 is the same as B6, then the cell value should be 1, if it isn't, the cell value should be 0.


If you want the comparison only to be with letters then...

=IF(NOT(ISNUMBER(B5)), IF(C5=B5,1,0), "")

This will only put a 1 or a 0 if there is a letter in B5. If the value in B5 is a number then the result will be blank.

I'm not sure this does what you want though.
Author:  rustybucket [ Mon May 23, 2011 10:41 pm ]
Post subject:  Re: Excel help
Fogmeister wrote:
=IF(NOT(ISNUMBER(B5)), IF(C5=B5,1,0), "")

This will only put a 1 or a 0 if there is a letter in B5. If the value in B5 is a number then the result will be blank.

I'm not sure this does what you want though.

Looks right to me...

...but I'm using LibreOffice so I daredn't test it.
Author:  Fogmeister [ Tue May 24, 2011 6:34 am ]
Post subject:  Excel help
rustybucket wrote:
Fogmeister wrote:
=IF(NOT(ISNUMBER(B5)), IF(C5=B5,1,0), "")

This will only put a 1 or a 0 if there is a letter in B5. If the value in B5 is a number then the result will be blank.

I'm not sure this does what you want though.

Looks right to me...

...but I'm using LibreOffice so I daredn't test it.

Lol, I was using libre office also :-)
Author:  l3v1ck [ Tue May 24, 2011 6:44 am ]
Post subject:  Re: Excel help
Fogmeister wrote:
=IF(IsNumber(B6), if(C6 = B6, 1, 0), "")
The formula seems fine. If I enter numbers in C5 and B5, it works exactly as it's supposed to.
The problem I have now is that my inputs in C5 and B5 are letters. Excel seems to decide that it doesn't want to do the calculation if that's the case. Stupid bloody MS.

I was a bit cheecky and tried: =IF(IsLetter(B6), if(C6 = B6, 1, 0), ""). Excel wasn't having any of it though.
Author:  jonlumb [ Tue May 24, 2011 6:45 am ]
Post subject:  Re: Excel help
Well use ISBLANK instead of ISNUMBER. If you need to distinguish between the two you're going to need to use nested IF statements

Edit: There is one minor caveat, in that if you have an IF statement or similar that puts the contents of "" into a cell, it will always fail an ISBLANK test.
Author:  l3v1ck [ Tue May 24, 2011 6:53 am ]
Post subject:  Re: Excel help
I'm still not getting a result if I use that, but have letters as the inputs in cells B5 and C5. Stupid bloody Excel.

I could try using Excel 2007 on my PC rather than Excel 2007 on my laptop. But even if it works on 2007 it's pointless. People who I'm sending it to don't all use 2007.
Author:  Fogmeister [ Tue May 24, 2011 7:55 am ]
Post subject:  Re: Excel help
OK, I'm at work now with proper Excel in front of me.

Could you explain again exactly what it is that you want the formula to do?

I'll have another go.

The first explanation said that it only wants to display 1 or 0 if B6 was a number but this seems to be not what you meant.
Author:  big_D [ Tue May 24, 2011 8:06 am ]
Post subject:  Re: Excel help
Code:
=if(isblank(b6),"Error",if(c6=b6,1,0))

Remove "Error" and replace it with "", once you have finished debugging.

Or, if you are checking for not a number:

Code:
=if(or(isblank(b6),isnumber(b6)),"Error",if(c6=b6,1,0))


Always test a positive first, even if the result you are after is not the positive, as checking for positives is much more efficient than searching for negatives. In Excel, you have to give actions for both true and false, so it doesn't make much difference to the formula. The "NOT" operative takes an extra couple of clock cycles to calculate, which isn't much nowadays and you won't notice any difference on one formula, but if that is spread over thousands of rows, it can make a huge difference! So it is something you should try and keep in mind, when writing formula.
Author:  l3v1ck [ Tue May 24, 2011 8:50 am ]
Post subject:  Re: Excel help
big_D wrote:
Or, if you are checking for not a number:

Code:
=if(or(isblank(b6),isnumber(b6)),"Error",if(c6=b6,1,0))


Thanks. That seems to work.

Thanks for all the suggestions guys.
Page 1 of 1 All times are UTC
Powered by phpBB® Forum Software © phpBB Group
https://www.phpbb.com/