Simple Formula Help

[Corey]

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?

 

[Bob Bridges]

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]

I had: =IF(G6<>"",IF((G6/G7-1)*100>0,(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....

 

[Corey]

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.

Corey....

 

[Bob Bridges]

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.

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.

I had: =IF(G6<>"",IF((G6/G7-1)*100>0,(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.