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.

Thanks
I'll give it a try.

Tried it with and without the spaces inbetween characters, but the result is always blank.

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...



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.

Looks right to me...

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

Lol, I was using libre office also

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.

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.

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.

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.

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

Or, if you are checking for not a number:



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.

Thanks. That seems to work.

Thanks for all the suggestions guys.