I am trying to set a simple formula as follows:

H7: Result of Formula

G6: Original Value ($20.69)

G7: New Value ($10.28)


H7 is Formatted to (%).

I want IF G6<"" or G^<0 then the Percentage to be Saved in Cell H7, else Nothing to be displayed.
Also, if the New Value (G7) is greater than the Origianl Value, then the Result (H7) to Show a Red Minus(-) Value.

I keep getting odd values that are not correct.

Can someone help me out?


--
Corey ....
The Silliest Question is generally
the one i forgot to ask.

That doesn't sound too hard. One hint I can give you is that for two
not-equal conditions you want not OR but AND -- if G6<"" AND G6<0. Or if
you want to stick with OR, use the positive conditions instead of the
negative: if G6="" OR G6=0, then you want nothing as the result, else you
want the percentage.

As for the rest, tell me what you've tried and I'll pick at it a bit.

--- "Corey" wrote:
I am trying to set a simple formula as follows:

H7: Result of Formula
G6: Original Value ($20.69)
G7: New Value ($10.28)

H7 is Formatted to (%).

I want IF G6<"" or G^<0 then the Percentage to be Saved in Cell H7, else
Nothing to be displayed. Also, if the New Value (G7) is greater than the Origianl
Value, then the Result (H7) to Show a Red Minus(-) Value. I keep getting odd
values that are not correct.

I had: =IF(G6<"",IF((G6/G7-1)*1000,(G6/G7-1)*100,""),"")
If i do a simple:
G7/G6 i get the correct (49.68%) saving, but
if i change the G6 to a value <G7 i get a larger % value, where it should be
much smaller??

I can get the IF statement correct with:
=IF(AND(G6<"",G6<0),
BUT it is the Correct % calculation and having the (-%) in red if that isa
the case.

Corey....

"Bob Bridges" wrote in message
...
That doesn't sound too hard. One hint I can give you is that for two
not-equal conditions you want not OR but AND -- if G6<"" AND G6<0. Or
if
you want to stick with OR, use the positive conditions instead of the
negative: if G6="" OR G6=0, then you want nothing as the result, else you
want the percentage.

As for the rest, tell me what you've tried and I'll pick at it a bit.

--- "Corey" wrote:
I am trying to set a simple formula as follows:

H7: Result of Formula
G6: Original Value ($20.69)
G7: New Value ($10.28)

H7 is Formatted to (%).

I want IF G6<"" or G^<0 then the Percentage to be Saved in Cell H7,
else
Nothing to be displayed. Also, if the New Value (G7) is greater than the
Origianl
Value, then the Result (H7) to Show a Red Minus(-) Value. I keep getting
odd
values that are not correct.

Think i craked it with:
=IF(OR(G6<"",G6<0),(G6-G7)/G6,"")
And used CF to change Font Colour and Shding to suit IF value is Less than
0.

Nothing wrong with conditional formatting, exactly, excepting only that it's
sometimes hard to remember it's in place, later on -- if indeed by that time
your sheet isn't being supported by someone who never knew it in the first
place. Try this: Set the % format in the normal way, then switch that
cell(s) to Custom formatting and add a special format for negative numbers,
like this: "0%;[red]-0%".

All custom formats have 1 to 4 format strings separated by semicolon (";").
Each string represents the format for (in this order) positive numbers,
negative numbers, 0, and text. If any semicolon is missing, the subsequent
format strings are left at default values. Thus the above string tells Excel
that a positive number should be displayed as a percentage ("0%"), a negative
number as a percentage in red with a leading minus sign ("[red]-0%"), and
that 0 values and text value should be displayed in some default manner.
That has the same effect as conditional formatting, but it's easier to spot
when you have to maintain this sheet a year from now.

--- "Corey" wrote:
Think i cra[c]ked it with =IF(OR(G6<"",G6<0),(G6-G7)/G6,""), and used CF to
change Font Colour and Shding to suit IF value is Less than 0.

--- "Corey" wrote in message
...
I had: =IF(G6<"",IF((G6/G7-1)*1000,(G6/G7-1)*100,""),"")
If i do a simple G7/G6 i get the correct (49.68%) saving, but if i change the G6
to a value <G7 i get a larger % value, where it should be much smaller??

I can get the IF statement correct with =IF(AND(G6<"",G6<0), BUT it is the
Correct % calculation and having the (-%) in red if that isa the case.

--- "Bob Bridges" wrote in message
...
That doesn't sound too hard. One hint I can give you is that for two
not-equal conditions you want not OR but AND -- if G6<"" AND G6<0. Or
if you want to stick with OR, use the positive conditions instead of the
negative: if G6="" OR G6=0, then you want nothing as the result, else
you want the percentage.

As for the rest, tell me what you've tried and I'll pick at it a bit.

--- "Corey" wrote:
I am trying to set a simple formula as follows:

H7: Result of Formula
G6: Original Value ($20.69)
G7: New Value ($10.28)

H7 is Formatted to (%).

I want IF G6<"" or G^<0 then the Percentage to be Saved in Cell H7,
else Nothing to be displayed. Also, if the New Value (G7) is greater than the
Origianl Value, then the Result (H7) to Show a Red Minus(-) Value. I keep
getting odd values that are not correct.