Need to debug and excel IF statement

May 2, 2011 at 17:50:28
Specs: Windows XP
debug an excel IF statement
=if(a1<>b1,"ERROR,"")

a1= 1234.56
b1= c1*d1


See More: Need to debug and excel IF statement

Report •


#1
May 2, 2011 at 17:55:24
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 statement

Is that an order or a question?

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


Report •

#2
May 2, 2011 at 17:59:49
sorry...
i keep getting "ERROR" displayed when the values in the two cells
are the same.

Report •

#3
May 2, 2011 at 18:25:20
any ideas on whats wrong with my if statement?

Report •

Related Solutions

#4
May 2, 2011 at 18:49:15
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 exactly 1234.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.


Report •

#5
May 2, 2011 at 19:28:59
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%


Report •

#6
May 2, 2011 at 19:34:55
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.


Report •

#7
May 2, 2011 at 19:38:33
How do I round to 2 decimal places in the calc? When I ROUND 2214.8598000000 I get 2215 but I want 2214.86

Report •

#8
May 2, 2011 at 20:48:49
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_digits argument?

Try that.

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


Report •

#9
May 3, 2011 at 14:50:44
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) ???


Report •

#10
May 3, 2011 at 18:00:19
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.


Report •

#11
May 4, 2011 at 14:37:31
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.

Report •

#12
May 4, 2011 at 16:24:43
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

http://www.skeptic.com/


Report •

#13
May 4, 2011 at 19:41:51
re: "Can I do =ROUND((D6*B27),2)"

Then you did try 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 exactly 31.6% and D6 equals exactly 7009.05
then =ROUND((D6*B27),2) will return exactly 2214.86.

When that cell is compared to another cell containing exactly 2214.86 they will be found to be exactly equal.

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.


Report •


Ask Question