+ Reply to Thread
Results 1 to 7 of 7

"IF" statements for text and numbers for financial summary

  1. 10-07-2013, 01:33 PM #1
    amartino44
    amartino44 is offline
    Forum Contributor
    Join Date
    12-12-2012
    Location
    Los Angeles
    MS-Off Ver
    Excel 2010
    Posts
    448

    "IF" statements for text and numbers for financial summary

    I have attached a spreadsheet. I would like to create the following sentence based on the numbers in the spreadsheet. I have problems with creating the If statements and the text. The numbers can be negative or positive depending on the results and this changes the words in the sentence. Thanks

    Our company reported a 20.8 Pretax profit (loss) for September which is 1.5 more (lower) than prior year and 3.6 higher (below) prior year.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 10-07-2013, 01:53 PM #2
    Ace_XL
    Ace_XL is offline
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: "IF" statements for text and numbers for financial summary

    Try

    ="Our company reported a "&TEXT(ABS(B3),"0.0")&" Pretax "&IF(B3>=0,"profit","loss")&" for "&A1&" which is "&TEXT(ABS(C4),"0.0")&" "&IF(C4>=0,"more","lower") &" than prior year and "&TEXT(ABS(D4),"0.0")&" "&IF(D4>=0,"higher","below")&" prior year. "
    Life's a spreadsheet, Excel!
    Say thanks, Click *
    Reply With QuoteRegister To Reply
  3. 10-07-2013, 01:57 PM #3
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,048

    Re: "IF" statements for text and numbers for financial summary

    Try this 1...
    ="Our company reported a "&ABS(B3)&" Pretax "&IF(B3>0,"profit","loss")&" for "&$A$1&" which is "&ABS(C4)&IF(C4>0," more"," lower")&" than prior year and "&ABS(D4)&IF(D4>0," higher"," below")&" prior year."
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford
    Reply With QuoteRegister To Reply
  4. 10-07-2013, 01:59 PM #4
    AlKey
    AlKey is offline
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: "IF" statements for text and numbers for financial summary

    Try this

    ="Our company reported a "&TEXT(B3,"#.0")&" Pretax profit (loss) for September which is "&TEXT(C4,"#.0")&" more (lower) than prior year and "&TEXT(D4,"#.0")&" higher (below) prior year."
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne
    Reply With QuoteRegister To Reply
  5. 10-07-2013, 02:09 PM #5
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,048

    Re: "IF" statements for text and numbers for financial summary

    AlKey, I think they want to have either more OR lower, depending on the value?
    Reply With QuoteRegister To Reply
  6. 10-07-2013, 02:20 PM #6
    AlKey
    AlKey is offline
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: "IF" statements for text and numbers for financial summary

    Quote Originally Posted by FDibbins View Post
    AlKey, I think they want to have either more OR lower, depending on the value?
    Yea, I forgot about that. I will blame it on my ADD
    Reply With QuoteRegister To Reply
  7. 10-07-2013, 02:50 PM #7
    sktneer
    sktneer is offline
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,655

    Re: "IF" statements for text and numbers for financial summary

    Please see the attached workbook. I hope it works as per your requirement.
    Attached Files Attached Files
    Regards
    sktneer

    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Run Macros on all except "Summary" and "Reports" worksheets in a workbook
    By daralea in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-10-2013, 05:42 PM
  2. [SOLVED] Need Macro to compile data from "Weekly" sheets and sum up into "Monthly" summary sheet
    By mo4391 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-21-2013, 07:25 PM
  3. A function to change "text" to "numbers" so the result can be calculated.
    By skip2 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 09-05-2012, 10:23 AM
  4. So Many Numbers, Too Little Knowledge. What to do with more than 7 "IF,OR" statements?
    By brokerbreaker in forum Excel General
    Replies: 3
    Last Post: 06-29-2012, 11:12 AM
  5. Replace numbers with Text ("1" to "Apple", for example)
    By pilotwings64 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-07-2008, 06:56 PM

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1