Results 1 to 13 of 13
  1. #1
    New Lounger
    Join Date
    Jun 2015
    Posts
    7
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Help with an IF statement in Excel

    Hi folks, this my first post and I hope I am in the right place. I am using Excel in Office 2007
    I have this If statement =IF(B4=0,"Market closed",(11.5*B4)). The statement works fine but I would like it to print the text string, Market closed in his case, in red.

    Thanks for any help.

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    R.R.,

    Welcome to the Lounge as a new poster!

    You can use conditional formatting to accomplish your task.

    1. Select the cell containing the formula.
    2. On the Home Tab select Conditional Formatting.
    3. Select New Rule.
    4. Select Use a formula to determine which cells to format.
    5. Fill in the boxes:
      CondFmt.PNG
      Note the formula is: =A1="Market Closed"
    6. Click OK.



    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. The Following User Says Thank You to RetiredGeek For This Useful Post:

    Redryder45 (2016-06-27)

  4. #3
    New Lounger
    Join Date
    Jun 2015
    Posts
    7
    Thanks
    5
    Thanked 0 Times in 0 Posts
    I guess I left out an important piece of information. I m pretty much a beginner. I folowed the steps but nothing happened

    =IF(B3=0,"Market closed",(11.5*B3)) This IF statement is in B3 is in cell B3
    The date is in cell A3. A dollar amount is in B3. t he formula is in C3.
    5/1/2016 $0.00 Market closed
    5/2/2016 $25.00 $287.50
    5/3/2016 $12.00 $138.00
    5/4/2016 $10.00 $115.00
    5/5/2016 $21.00 $241.50
    Last edited by Redryder45; 2016-06-27 at 17:53.

  5. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,631
    Thanks
    115
    Thanked 645 Times in 589 Posts
    =IF(B3=0,"Market closed",(11.5*B3)) This IF statement is in B3 is in cell B3
    Redryder,

    Above, you are saying you have a formula in Cell B3 that references itself (circular reference).

    Cell B3 =IF(B3=0,"Market closed",(11.5*B3))

    You cannot do this in Excel

    I think what you meant was

    Cell C3 =IF(B3=0,"Market closed",(11.5*B3))

    This can also be done using a user defined function

    In a standard module, enter the following code:
    Code:
    Public Function Market(rng As Range)
    Market = IIf(rng = 0, "Market Closed", rng * 11.5)
    If Market = "Market Closed" Then
        Application.Caller.Font.Color = vbRed
    Else:
        Application.Caller.Font.Color = vbBlack
    End If
    End Function
    In Cell C3 enter the following formula:
    cell C3 = Market(B3) then copy down

    Market1.png

    HTH,
    Maud
    Attached Files Attached Files

  6. The Following User Says Thank You to Maudibe For This Useful Post:

    Redryder45 (2016-06-27)

  7. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    R.R.,

    Ok now I understand. If you want to do this w/o code you can use conditional formatting as follows:

    1. Create a Dynamic Range Name for column C. I chose to call it MarketValue and it is defined like this:
      1. In the Name Manager on the Format Tab.
      2. Click New
      3. Type the name in the Name: box
      4. In the Scope box select the sheet name as appropriate, in the enclosed example Sheet1.
      5. In the Refers to: box type the formula below. (This allows for up to 10,000 rows!)
        =OFFSET(Sheet1!$C$3,0,0,COUNTA(Sheet1!$C$3:$C$1000 2),COUNTA(Sheet1!$C$3:$C$3))
      6. Click OK.
    2. Highlight the used values in Column C starting in C3.
    3. Click Conditional Formatting on the Home Tab.
    4. Click New Rule
    5. Click Use a formula to determine which cells to format.
    6. In the Edit the Rule Description: box type:
      =UPPER($C3)="MARKET CLOSED"
    7. Click the Format... button.
    8. Set the format the way you want it.
    9. Click OK.


    You're done. As you add rows the conditional format will automatically apply to Column C and you can fill the formula in column C down as necessary. Actually the formula seems to be auto filling!

    Try changing the value in Column B to zero and see what happens.

    Test File: Market-RGV1.xlsx

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  8. The Following User Says Thank You to RetiredGeek For This Useful Post:

    Redryder45 (2016-06-27)

  9. #6
    New Lounger
    Join Date
    Jun 2015
    Posts
    7
    Thanks
    5
    Thanked 0 Times in 0 Posts
    We are getting lost here somehow. Here is a screen shot http://screencast.com/t/2who1SWVIcZ

    As you can see Cell A3 contains only the date
    Cell B3 will contain a number.
    Cell C3 contains the If statement. The If statement looks at cell B3 and if it sees a zero it prints Market Closed, if it sees a number greater than zero it multiplies that number times 11.5 and puts the answer in cell C3.

    That all works fine there is no circular reference.

    What I would like to now is not only print Market Closed but to also print it in RED.

    I hope that is a better explanation this time. As I said I am a bit of a newbie at this Excel game.

    Thanks for your help so far.

  10. #7
    New Lounger
    Join Date
    Jun 2015
    Posts
    7
    Thanks
    5
    Thanked 0 Times in 0 Posts
    I will see if I can figure that one out. LOL
    I appreciate your help.

  11. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi

    You can create a custom format to give you exactly what you want.
    Excel custom formats are defined in four Sections, separated by semicolons:
    Positive numbers; Negative Numbers; Zero Values ;Text values.
    Text is only affected by custom formats when you use all four sections.
    Text would use the last section.

    so you could :
    select your formula range and apply this custom format to that range:
    $#,##0.00;;;[Red]General

    see attached file

    zeddy
    Day 15 in hospital
    Attached Files Attached Files

  12. The Following User Says Thank You to zeddy For This Useful Post:

    Redryder45 (2016-06-29)

  13. #9
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,469
    Thanks
    30
    Thanked 61 Times in 57 Posts
    ZEDDY: why have you been in the hospital for 15 days???

  14. #10
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    R.R.,

    Did you check out the test file in my last post?
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  15. #11
    New Lounger
    Join Date
    Jun 2015
    Posts
    7
    Thanks
    5
    Thanked 0 Times in 0 Posts
    I am sorry, I couldn't get it to work but following your formula I think my workbook is set up wrong. I started playing a bit today but didn't have much time. Probbaly won't tomorrow either but I think I am beginning to see waht you are doing.
    Thanks

  16. #12
    New Lounger
    Join Date
    Jun 2015
    Posts
    7
    Thanks
    5
    Thanked 0 Times in 0 Posts
    I will try your suggestion too. It never hurts to have more than one solution. I hope you get out of the hospital soon. They certainly arent my favorite places.

  17. #13
    New Lounger
    Join Date
    Jun 2015
    Posts
    7
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Problem solved. Thank you all for the interest. I used the custom number format from Zeddy because it was the simplest for me.
    I still plan to try to use RetiredGeek's answer "Create a Dynamic Range Name for column C" when I have some time.
    Redryder45

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •