Assistance with a formula

B

Bookmdano

I am comparing data from 2 columns. What I want to do is have a formula that
says if A1-B1>10,000 and (A1-B1)/A1> 10% then highlight cell C1. Can this be
done?

Thanks
 
F

FSt1

hi
formulas return values, they cannot perform actions such as highlight cells.
but if you put the formula in conditional formating, it would work.
formula is......=IF(AND(A1-B1>10000,(A1-B1)/A1>0.1),1,0)...pick your color.

regards
FSt1
 
B

Barb Reinhardt

If you are talking about formulas in conditional formats, you don't need the IF

AND(A1-B1>10000,(A1-B1)/A1>0.1)

is all you need.

Just put it in the cell that needs the condition set.

Look, I didn't hit enter between Subject and message. :)
 
F

FSt1

cool. gaining control.

thanks
regards
FSt1

Barb Reinhardt said:
If you are talking about formulas in conditional formats, you don't need the IF

AND(A1-B1>10000,(A1-B1)/A1>0.1)

is all you need.

Just put it in the cell that needs the condition set.

Look, I didn't hit enter between Subject and message. :)
Click to expand...
 
B

Bookmdano

Thanks! Both solutions give me what I need. I like the True/False response.
But is there anyway to highlight or change font color if there is a True
response?
 
B

Bookmdano

Thanks for the assitance but I have a new twist. I also need a "True" return
if A1=0 and B1>10,000. Since I'm dividing by zero (A1) my response is #DIV/0!

Any suggestions?
 
F

FSt1

yes.
add a second condition, same formula but reverse the true/false part.

regards
FSt1
 
F

FSt1

hi
are you using conditional formating. if so, you should not get a #Div/0
error. only
not format in cell. if your are using just the fomula try this.
=IF(A1=0,"",IF(AND(A1-B1>10000,(A1-B1)/A1>0.1),1,0))

regard
FSt1
 
B

Bookmdano

I wasn't using conditional formating. I was just using the formula. I tried
your revised formula but the response was not what I needed. Here's my
problem:
I am trying to get a True response whenever there was a change of 10,000 or
10%.

2008 2007
Cash 110,000 100,000
Deposits 0 12,000
Receivables 15,000 0
Interest 30,000 15,000

Based on the above data I would expect Cash = False, Deposits =True,
Receivables =True and Interest = True.

Now I just need a formula to figure this out. One that when divided by a
negative number gives me a True or False response.

Any suggestions?
 
B

Bookmdano

I tried using this formula and this is what I got
B2 1650 C2 0.00 = True but should be False
B3 0.00 C3 30,000 = False but should be True
B4 0.00 C4 0.00 = #Div/0! (Any way to change that?)

Thanks for your assistance.
 
B

Bookmdano

Correction. The 2nd example (B3) was correct. I did get a True response. Not
sure what happened in B2.
 
B

Bookmdano

The problem I do get is when B3 is zero and C3 is a negative number. The
response I get is #DIV/0!
 
D

David Biddulph

That formula (which gives the TRUE/ FALSE response) was what was recommended
you should put into your Conditional Formatting/ Formula IS condition.
Just remember to put the = at the beginning of the formula, bercause Excel
doesn't assume it, even in CF/ Formula Is.
 
D

David Biddulph

What do you think is the problem with the output of the formula using B2?
Isn't 100% greater than 10% ? Why do you think it should be False?
 
B

Bookmdano

I only want a True response if the the change between years is greater than
10,000 and 10%. Also, the formula in calculating the results of B3 and C3
only returns a True response if the number is positive. Not recognizing
negative numbers if the cell it's being compared to is zero.
 
D

David Biddulph

If you only want a True response if the the change between years is greater
than 10,000 and 10%, why have you used OR, rather than AND?

As for your problem with negative numbers, you have asked the formula to
look at MAX(B2,C2), so obviously if one of those is negative and the other
is zero, the MAX will give zero. If you want to look at the MAX of the ABS
values, you need to tell the formula to do that.

And if you are concerned about the case where both inputs are zero, you
probably want to trap for that in a first IF test, and then go on to your
existing test (modified to suit your changed requirements) if either input
is non-zero.
 
B

Bookmdano

I did change the Or to an And and that solved one other problem I was having.
How do I tell the formula to look at the MAX of the ABS values?

This is what I have so far:
=AND(ABS(D8-E8)>1000000,(ABS(D8-E8)/MAX(D8,E8))>10%)
Works great with everything except when cell B2 is zero and cell C2 is a
negative number.

I can live with the response when both cells are zeroes/
 
F

Fred Smith

"How do I tell the formula to look at the MAX of the ABS values?"

MAX(ABS(D8),ABS(E8))

Regards,
Fred.
 
Top