Solved Need help on IF Statement:

Microsoft Office excel 2007 - upgrade
July 4, 2012 at 17:33:51
Specs: Windows 7
Actuals Fcst
$56 $68 $13 19%
$0 $0 $0 94%

I'm trying to get the %'s to calculate. The 2nd set of numbers which are 0 (due to rounding) is calculating as 94% where it should be 0%, In addition, the result in % that is +/- 10% should be highlighted in color. Thank you.


See More: Need help on IF Statement:

Report •

#1
July 4, 2012 at 19:12:00
Please click on the blue line at the end of this post and read the instructions on how to post example data in this forum. Then repost your data.

I see two column headings but four columns of data. I can't tell what you are trying to do.

It would also help if you included the formulas that you are trying to use.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
July 4, 2012 at 22:52:33
A B C D
1 $56 $68 $13 19%
2 $0 $0 $0 94%
3 $26 $27 $0 1%

Note: $ are in thousands
$56 is actual, $68 is fcst, (B1-A1=C1) $13 (favorable), 19% (c1/B1)
=IF(C1>.499,C1/B1,0)
For the 2nd row, because of rounding, the % is high. It should be 0%. Then, I want to select all +/- 10% (favorable/unfavorable) and highlighted with blue color. I don't know how to incorporate it in the statement.


Report •

#3
July 5, 2012 at 01:31:48
✔ Best Answer
Either I'm missing something or you're not telling us something. Using your example data and formula, I believe you have this:

     A      B       C              D  
1   $56    $68   =B1-A1   =IF(C1>.499,C1/B1,0)
2    $0     $0   =B2-A2   =IF(C2>.499,C2/B2,0)
3    $2    $27   =B3-A3   =IF(C3>.499,C3/B3,0)

If that's correct, the results I get are these:

     A      B     C      D  
1   $56    $68   $12   17.65%
2    $0     $0    $0    0.00%
3   $2     $27    $1    3.70%

re: "For the 2nd row, because of rounding, the % is high. It should be 0%."

What rounding? I do 0%.

"Then, I want to select all +/- 10% (favorable/unfavorable) and highlighted with blue color. I don't know how to incorporate it in the statement. "

By "statement", I assume you mean "formula".

This is not done within the formula. This is done via a feature called Conditional Formatting. If you look up Conditional Formatting in the Help files or search Google, you should be able to figure out how to write the rule to format the cell based on the result of the formula.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •
Related Solutions


Ask Question