debug an excel IF statement

=if(a1<>b1,"ERROR,"")a1= 1234.56

b1= c1*d1

I know what's wrong with the formula you posted, but I'll wait until you actually ask for some help, and maybe even tell us what problems you are having.

debug an excel IF statementIs that an order or a question?

Click Here Before Posting Data or VBA Code --->How To Post Data or Code.

sorry...

i keep getting "ERROR" displayed when the values in the two cells

are the same.

any ideas on whats wrong with my if statement?

Well, as written in your post, you are missing a " after the word ERROR. So assuming you actually have the formula written correctly in your spreadsheet, it may be working just fine.

If C1 * D1 doesn't return

exactly1234.56, then the formula will return ERROR.1234.5600000000001 is not equal to 1234.56, so if B1 is formatted to display 2 decimal places and shows 1234.56, Excel is still going to use 1234.5600000000001 when it compares the value to A1.

You may need to wrap a ROUND function around C1 * D1 to ensure that it returns a value that only has 2 decimal places.

Click Here Before Posting Data or VBA Code --->How To Post Data or Code.

Thanks for your note. Yes, the missing " is not the issue. Both cells are Category 'Number' with 2 decimal places. I tried ROUNDing but that didn't make a difference. Here are the actual values:

I am comparing a constant $2214.86 with =D6*B27 where D6 = $7009.05

and B27 is 31.60%

I expanded both cells to 10 decimal places: 2214.8600000000 (this is the constant)

vs

2214.8598000000 (this is the product)If I format the two cells to 2 decimal places it still doesn't work.

How do I round to 2 decimal places in the calc? When I ROUND 2214.8598000000 I get 2215 but I want 2214.86

re: " If I format the two cells to 2 decimal places it still doesn't work."Of course not. Formatting only controls what is displayed, not the results of the formula. That's what I explained in Response #4.

re: "

When I ROUND 2214.8598000000 I get 2215..."Did you read the Excel Help file for ROUND?

ROUND(number,

num_digits)Is it safe to assume that you didn't use the

num_digitsargument?Try that.

Click Here Before Posting Data or VBA Code --->How To Post Data or Code.

Thanks for your help thus far. I am trying to round the result of =D6*B27

The value of D6 is $7009.05

The value of B27 is 31.6%

Can I do =ROUND((D6*B27),2) ???

I'm not sure why you are asking if you can do it instead of just trying it. Learning by doing.

Click Here Before Posting Data or VBA Code --->How To Post Data or Code.

I tried it and it didn't work - I thought I'd display the syntax I used to see if anyone can tell me what's wrong with it - instead I get a wise crack answer. Hmmm.

I tried your formula: =ROUND((D6*B27),2)

With 7009.05 in cell D6

and 31.6% in B27

and I got: 2214.86

What exactly did you get?

In what way did it not work?MIKE

re: " Can I do =ROUND((D6*B27),2)"Then you

didtry it, right? I can't see your spreadsheet from where I'm sitting, so how was I supposed to know that?If you tried it and it didn't work, why didn't you tell us that?

If I had simply answered "Yes, you can do it" would that have been a better answer?

As Mike has said, your formula works fine. When used as you described, the IF function that started this thread returns a blank cell, as expected.

So, since the values you posted work for both Mike and I, that tells me one thing:

At least one of your input values must not be what you think it is.

If B27 equals

exactly31.6% and D6 equalsexactly7009.05

then =ROUND((D6*B27),2) will returnexactly2214.86.When that cell is compared to another cell containing

exactly2214.86 they will be found to beexactlyequal.Try this: Copy and Paste these formulas into some cells and let us know if any of them return FALSE.

They will compare the values in your cells against the hard coded values in the formula to see if your cells really contain what you think they do.

=D6=7009.05

=B27=31.6%

=(whatever cell contains your constant)=2214.86

Click Here Before Posting Data or VBA Code --->How To Post Data or Code.

Ask Your Question

Weekly Poll