output of if statement

  • Thread starter Thread starter Steven Platt
  • Start date Start date
S

Steven Platt

Say I have a value in A1 and a value in B1. I would like to display these
two in percentage form in C1. However, I would like there to be a visual
difference if one is greater than the other. For example:
A1 is 40
B1 is 100
C1 would output 150%

A1 is 100
B1 is 40
C1 would output (40%)
basically meaning that B1(this year) has not met the number of A1(last year)
yet. I have no idea how to start this. I was trying to use a simple IF
statement with a little trickery on the output. But I couldn't figure it
out. I also need to make sure that when B1 has exceeded A1 that 100 is
subtracted from the output. Normally, in the first case, B1/A1 = 250%. But
I would like it to display 150%. Thanks in advance for any help.

-Steven-
 
It is a little deeper than that Anne. I don't know why it is hard to
understand, I will try to explain better.
A1 = 40 (This represents last year's products sold)
B1 = 100 (This represents this year's products sold)
C1 = 150% (This represents the percentage of extra products sold)
notice how C1 is not 250% .. basically I would like to subtract 100% from
the output(B1/A1). So in a meeting you can say "We have sold 150% _more_
than last year." That way everyone listening would know to take 40 + 1.5*40
and get 100.

The next example demonstrates what would happen if A1(last year's products
sold) was greater than B1(this year's products sold).
A1 = 100
B1 = 40
C1 = (40%)
Notice how this is formatted. I would like it to have parentheses around it
indicating that the previous year's sales(A1) has not been reached yet. I
was thinking I could do something with an IF statement. Something like
IF(A1>B1), "<display precent in ()>", "<display percent - 100>".

Hopefully this makes more sense.. I really need help on this one.

-Steven-
 
Hi Steven,

A1: 40
B1: 100
C1: =IF(isnumber(A1),(B1-A1)/A1,"") format as % (1.5 is same as 150%)

Select C1, then Select Column C so that C1 is the active cell
Format, Conditional Formatting,
Formula 1 is: =$B1>$A1 format button, pattern, choose a pastel green
Formula 2 is: =$B1<$A1 format button, pattern, choose a pastel yellow

The object of Conditional Formatting (and Filters) is to provide a True or False
indication. zero is false, everything else is true, an error will be ignored.

Have used absolute column numbers, so that if you prefer to color the
entire row, you can start with any cell on the first row selected,
and then use Ctrl+A (or if using Excel 2003 use Ctrl+Shift+SpaceBar)

page on Conditional Formatting:
http://www.mvps.org/dmcritchie/excel/condfmt.htm
 
Oh! And the parens, is just a custom format:

0.00%_);(0.00%)

If you only want one or two decimals, I think you can figure that out? Just
delete the zeroes beyond the decimal, or even the decimal itself, leaving at
least one zero on each side.

*******************
~Anne Troy

www.OfficeArticles.com
www.MyExpertsOnline.com
 
B1/A1 = 40/100 = .4 .. as percent it is 40%. I am interested in how much of
the previous year's product has been sold. Not how much there is left to
meet the previous year. And your second post will not solve my subtracting
100 problem. I don't want this to turn into a math discussion. I would
just like to format my output. I am not interested in making them different
colors either. I would like it to look like (in English) this:
=IF((A1>B1), "<format percent(a.k.a (B1/A1)) with () around it>", "<format
percent(a.k.a. (B1/A1)) - 100>" )

I have seen this in many spreadsheets before. I wish I knew how to do it.
Is this really that complicated that I have to do it some other way?

-Steven-
 
That is as far as I have gotten. I would now like to put parentheses around
the value_if_true part of that. Is that possible? Thank you so much Anne
for being patient with me.

-Steven-
 
That would be fine and dandy if it only applied to the value_if_true part of
the IF statement. I only want the value to be in parentheses if A > B.
Does that make sense?

-Steven-
 
Back
Top