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

Thanks

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-B110000,(A1-B1)/A10.1),1,0)...pick your color.

regards
FSt1

"Bookmdano" wrote:

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

Thanks

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

AND(A1-B110000,(A1-B1)/A10.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. :)
--
HTH,
Barb Reinhardt



"FSt1" wrote:

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-B110000,(A1-B1)/A10.1),1,0)...pick your color.

regards
FSt1

"Bookmdano" wrote:

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

Thanks

cool. gaining control.

thanks
regards
FSt1

"Barb Reinhardt" wrote:

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

AND(A1-B110000,(A1-B1)/A10.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. :)
--
HTH,
Barb Reinhardt



"FSt1" wrote:

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-B110000,(A1-B1)/A10.1),1,0)...pick your color.

regards
FSt1

"Bookmdano" wrote:

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

Thanks

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?

"Barb Reinhardt" wrote:

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

AND(A1-B110000,(A1-B1)/A10.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. :)
--
HTH,
Barb Reinhardt



"FSt1" wrote:

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-B110000,(A1-B1)/A10.1),1,0)...pick your color.

regards
FSt1

"Bookmdano" wrote:

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

Thanks

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

Any suggestions?

"Barb Reinhardt" wrote:

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

AND(A1-B110000,(A1-B1)/A10.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. :)
--
HTH,
Barb Reinhardt



"FSt1" wrote:

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-B110000,(A1-B1)/A10.1),1,0)...pick your color.

regards
FSt1

"Bookmdano" wrote:

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

Thanks

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

regards
FSt1

"Bookmdano" wrote:

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?

"Barb Reinhardt" wrote:

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

AND(A1-B110000,(A1-B1)/A10.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. :)
--
HTH,
Barb Reinhardt



"FSt1" wrote:

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-B110000,(A1-B1)/A10.1),1,0)...pick your color.

regards
FSt1

"Bookmdano" wrote:

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

Thanks

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-B110000,(A1-B1)/A10.1),1,0))

regard
FSt1

"Bookmdano" wrote:

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

Any suggestions?

"Barb Reinhardt" wrote:

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

AND(A1-B110000,(A1-B1)/A10.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. :)
--
HTH,
Barb Reinhardt



"FSt1" wrote:

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-B110000,(A1-B1)/A10.1),1,0)...pick your color.

regards
FSt1

"Bookmdano" wrote:

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

Thanks

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?

"FSt1" wrote:

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-B110000,(A1-B1)/A10.1),1,0))

regard
FSt1

"Bookmdano" wrote:

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

Any suggestions?

"Barb Reinhardt" wrote:

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

AND(A1-B110000,(A1-B1)/A10.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. :)
--
HTH,
Barb Reinhardt



"FSt1" wrote:

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-B110000,(A1-B1)/A10.1),1,0)...pick your color.

regards
FSt1

"Bookmdano" wrote:

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

Thanks

Hi

Try
=OR(ABS(B2-C2)10000,(ABS(B2-C2)/MAX(B2,C2))10%)

--
Regards
Roger Govier

"Bookmdano" wrote in message
...
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?

"FSt1" wrote:

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-B110000,(A1-B1)/A10.1),1,0))

regard
FSt1

"Bookmdano" wrote:

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

Any suggestions?

"Barb Reinhardt" wrote:

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

AND(A1-B110000,(A1-B1)/A10.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. :)
--
HTH,
Barb Reinhardt



"FSt1" wrote:

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-B110000,(A1-B1)/A10.1),1,0)...pick
your color.

regards
FSt1

"Bookmdano" wrote:

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

Thanks

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.

"Roger Govier" wrote:

Hi

Try
=OR(ABS(B2-C2)10000,(ABS(B2-C2)/MAX(B2,C2))10%)

--
Regards
Roger Govier

"Bookmdano" wrote in message
...
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?

"FSt1" wrote:

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-B110000,(A1-B1)/A10.1),1,0))

regard
FSt1

"Bookmdano" wrote:

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

Any suggestions?

"Barb Reinhardt" wrote:

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

AND(A1-B110000,(A1-B1)/A10.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. :)
--
HTH,
Barb Reinhardt



"FSt1" wrote:

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-B110000,(A1-B1)/A10.1),1,0)...pick
your color.

regards
FSt1

"Bookmdano" wrote:

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

Thanks

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

"Bookmdano" wrote:

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.

"Roger Govier" wrote:

Hi

Try
=OR(ABS(B2-C2)10000,(ABS(B2-C2)/MAX(B2,C2))10%)

--
Regards
Roger Govier

"Bookmdano" wrote in message
...
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?

"FSt1" wrote:

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-B110000,(A1-B1)/A10.1),1,0))

regard
FSt1

"Bookmdano" wrote:

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

Any suggestions?

"Barb Reinhardt" wrote:

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

AND(A1-B110000,(A1-B1)/A10.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. :)
--
HTH,
Barb Reinhardt



"FSt1" wrote:

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-B110000,(A1-B1)/A10.1),1,0)...pick
your color.

regards
FSt1

"Bookmdano" wrote:

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

Thanks

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

"Bookmdano" wrote:

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

"Bookmdano" wrote:

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.

"Roger Govier" wrote:

Hi

Try
=OR(ABS(B2-C2)10000,(ABS(B2-C2)/MAX(B2,C2))10%)

--
Regards
Roger Govier

"Bookmdano" wrote in message
...
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?

"FSt1" wrote:

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-B110000,(A1-B1)/A10.1),1,0))

regard
FSt1

"Bookmdano" wrote:

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

Any suggestions?

"Barb Reinhardt" wrote:

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

AND(A1-B110000,(A1-B1)/A10.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. :)
--
HTH,
Barb Reinhardt



"FSt1" wrote:

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-B110000,(A1-B1)/A10.1),1,0)...pick
your color.

regards
FSt1

"Bookmdano" wrote:

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

Thanks

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

"Bookmdano" wrote in message
...
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?

"Barb Reinhardt" wrote:

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

AND(A1-B110000,(A1-B1)/A10.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. :)
--
HTH,
Barb Reinhardt



"FSt1" wrote:

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-B110000,(A1-B1)/A10.1),1,0)...pick your
color.

regards
FSt1

"Bookmdano" wrote:

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

Thanks

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?
--
David Biddulph

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

"Bookmdano" wrote:

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.

"Roger Govier" wrote:

Hi

Try
=OR(ABS(B2-C2)10000,(ABS(B2-C2)/MAX(B2,C2))10%)

--
Regards
Roger Govier

"Bookmdano" wrote in message
...
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?

"FSt1" wrote:

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-B110000,(A1-B1)/A10.1),1,0))

regard
FSt1

"Bookmdano" wrote:

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

Any suggestions?

"Barb Reinhardt" wrote:

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

AND(A1-B110000,(A1-B1)/A10.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. :)
--
HTH,
Barb Reinhardt



"FSt1" wrote:

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-B110000,(A1-B1)/A10.1),1,0)...pick
your color.

regards
FSt1

"Bookmdano" wrote:

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

Thanks

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.

"David Biddulph" wrote:

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?
--
David Biddulph

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

"Bookmdano" wrote:

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.

"Roger Govier" wrote:

Hi

Try
=OR(ABS(B2-C2)10000,(ABS(B2-C2)/MAX(B2,C2))10%)

--
Regards
Roger Govier

"Bookmdano" wrote in message
...
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?

"FSt1" wrote:

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-B110000,(A1-B1)/A10.1),1,0))

regard
FSt1

"Bookmdano" wrote:

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

Any suggestions?

"Barb Reinhardt" wrote:

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

AND(A1-B110000,(A1-B1)/A10.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. :)
--
HTH,
Barb Reinhardt



"FSt1" wrote:

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-B110000,(A1-B1)/A10.1),1,0)...pick
your color.

regards
FSt1

"Bookmdano" wrote:

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

Thanks

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

"Bookmdano" wrote in message
...
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.

"David Biddulph" wrote:

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?
--
David Biddulph

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

"Bookmdano" wrote:

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.

"Roger Govier" wrote:

Hi

Try
=OR(ABS(B2-C2)10000,(ABS(B2-C2)/MAX(B2,C2))10%)

--
Regards
Roger Govier

"Bookmdano" wrote in message
...
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?

"FSt1" wrote:

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-B110000,(A1-B1)/A10.1),1,0))

regard
FSt1

"Bookmdano" wrote:

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

Any suggestions?

"Barb Reinhardt" wrote:

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

AND(A1-B110000,(A1-B1)/A10.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. :)
--
HTH,
Barb Reinhardt



"FSt1" wrote:

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-B110000,(A1-B1)/A10.1),1,0)...pick
your color.

regards
FSt1

"Bookmdano" wrote:

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

Thanks

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/

"David Biddulph" wrote:

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

"Bookmdano" wrote in message
...
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.

"David Biddulph" wrote:

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?
--
David Biddulph

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

"Bookmdano" wrote:

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.

"Roger Govier" wrote:

Hi

Try
=OR(ABS(B2-C2)10000,(ABS(B2-C2)/MAX(B2,C2))10%)

--
Regards
Roger Govier

"Bookmdano" wrote in message
...
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?

"FSt1" wrote:

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-B110000,(A1-B1)/A10.1),1,0))

regard
FSt1

"Bookmdano" wrote:

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

Any suggestions?

"Barb Reinhardt" wrote:

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

AND(A1-B110000,(A1-B1)/A10.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. :)
--
HTH,
Barb Reinhardt



"FSt1" wrote:

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-B110000,(A1-B1)/A10.1),1,0)...pick
your color.

regards
FSt1

"Bookmdano" wrote:

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

Thanks

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

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

Regards,
Fred.

"Bookmdano" wrote in message
...
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/

"David Biddulph" wrote:

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

"Bookmdano" wrote in message
...
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.

"David Biddulph" wrote:

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?
--
David Biddulph

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

"Bookmdano" wrote:

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.

"Roger Govier" wrote:

Hi

Try
=OR(ABS(B2-C2)10000,(ABS(B2-C2)/MAX(B2,C2))10%)

--
Regards
Roger Govier

"Bookmdano" wrote in
message
...
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?

"FSt1" wrote:

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-B110000,(A1-B1)/A10.1),1,0))

regard
FSt1

"Bookmdano" wrote:

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

Any suggestions?

"Barb Reinhardt" wrote:

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

AND(A1-B110000,(A1-B1)/A10.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. :)
--
HTH,
Barb Reinhardt



"FSt1" wrote:

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-B110000,(A1-B1)/A10.1),1,0)...pick
your color.

regards
FSt1

"Bookmdano" wrote:

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

Thanks

That did it! Thanks everyone for your assistance!!!!!

"Fred Smith" wrote:

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

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

Regards,
Fred.

"Bookmdano" wrote in message
...
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/

"David Biddulph" wrote:

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

"Bookmdano" wrote in message
...
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.

"David Biddulph" wrote:

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?
--
David Biddulph

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

"Bookmdano" wrote:

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.

"Roger Govier" wrote:

Hi

Try
=OR(ABS(B2-C2)10000,(ABS(B2-C2)/MAX(B2,C2))10%)

--
Regards
Roger Govier

"Bookmdano" wrote in
message
...
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?

"FSt1" wrote:

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-B110000,(A1-B1)/A10.1),1,0))

regard
FSt1

"Bookmdano" wrote:

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

Any suggestions?

"Barb Reinhardt" wrote:

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

AND(A1-B110000,(A1-B1)/A10.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. :)
--
HTH,
Barb Reinhardt



"FSt1" wrote:

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-B110000,(A1-B1)/A10.1),1,0)...pick
your color.

regards
FSt1

"Bookmdano" wrote:

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

Thanks