+ Reply to Thread
Results 1 to 5 of 5

=text formula delivers wrong result

  1. #1
    Registered User
    Join Date
    12-10-2010
    Location
    Singapore
    MS-Off Ver
    MS Excel for Mac 2011
    Posts
    51

    =text formula delivers wrong result

    The following formula in cell N23 delivers the correct mathematical result: 0.370165745856354
    =IF(N22>"",SUM(M19:M22)/SUM(M14:M17),
    IF(N21>"",SUM(M19:M21)/SUM(M14:M16),
    IF(N20>"",SUM(M19:M20)/SUM(M14:M15),
    N19)))

    In cell I23, with formula =CONCATENATE("Total 2019 travel: ",M23," days"," (",N23,")") the N3 result is displayed as 0.370165745856354. I want it however to be displayed as 37%

    I've tried to format the N23 result to present 37%, by embedding =text formula:
    =TEXT(IF(N22>"",SUM(M19:M22)/SUM(M14:M17),"0%"),
    TEXT(IF(N21>"",SUM(M19:M21)/SUM(M14:M16),"0%"),
    TEXT(IF(N20>"",SUM(M19:M20)/SUM(M14:M15),"0%"),
    TEXT(N19,"0%"))))
    That does provide the required percentage format in N23 and I23, but the mathematical result is wrong (iso 37% it now is 42%).

    What am I doing wrong?

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: =text formula delivers wrong result

    Why isn't this simply

    =TEXT(IF(N22>"",SUM(M19:M22)/SUM(M14:M17),
    IF(N21>"",SUM(M19:M21)/SUM(M14:M16),
    IF(N20>"",SUM(M19:M20)/SUM(M14:M15),
    N19))),"0%")

    ???
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  3. #3
    Registered User
    Join Date
    12-10-2010
    Location
    Singapore
    MS-Off Ver
    MS Excel for Mac 2011
    Posts
    51

    Re: =text formula delivers wrong result

    Simplicity is the most difficult thing to secure; it is the last limit of experience and the last effort of genius.

    Thank you so much. It works like a charm. Have a great day!

    Gijs

  4. #4
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: =text formula delivers wrong result

    I'm not sure why the percentage changed when you did it your way though.

  5. #5
    Registered User
    Join Date
    12-10-2010
    Location
    Singapore
    MS-Off Ver
    MS Excel for Mac 2011
    Posts
    51

    Re: =text formula delivers wrong result

    I didn't understand it either, Trust me, I've tried various alternatives - except yours - all to no avail, hence my call on this forum. Thanks again for your exquisite solution and lightning fast response!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. IF Formula gives the wrong result
    By Faustocruz in forum Excel Formulas & Functions
    Replies: 17
    Last Post: 02-04-2015, 11:23 AM
  2. Formula gives out wrong result, Help Please
    By jyuens13 in forum Excel General
    Replies: 5
    Last Post: 10-28-2014, 07:55 PM
  3. [SOLVED] Code result wrong but formula correct result
    By marreco in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-11-2014, 12:26 PM
  4. [SOLVED] Wrong result adding values of text boxes in a userform.
    By MariaPap in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 06-27-2014, 09:46 AM
  5. [SOLVED] formula returns wrong result
    By zplugger in forum Excel General
    Replies: 3
    Last Post: 03-12-2014, 01:17 PM
  6. Wrong result with IF formula
    By rizmomin in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-25-2012, 03:57 PM
  7. FormulaArray Which Delivers Text Instead of Formula
    By inwalkedbud in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-04-2007, 02:05 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1