+ Reply to Thread
Results 1 to 6 of 6

Give a estimate based on a result

  1. 01-29-2014, 11:39 AM #1
    ilberg
    ilberg is offline
    Registered User
    Join Date
    01-29-2014
    Location
    Aalborg, Denmark
    MS-Off Ver
    Excel 2010
    Posts
    2

    Give a estimate based on a result

    Hi

    I am in a bit of a pickle here... i have tried to search but no result yet..

    I have a cell where i calculate a value and want to display a value range instead of the specific value. for example if the result in cell A1=47,5% i want it to display 45-50%
    I have tried to use a combination of IF & AND functions. "=IF(AND((((E18+J18)-(E20+J20)))/(E18+J18)<0,5;(((E18+J18)-(E20+J20)))/(E18+J18))>0,45);"Between 45 -50%";IF(AND(((((E18+J18)-(E20+J20)))/(E18+J18))<0,55;(((E18+J18)-(E20+J20)))/(E18+J18))>0,5);"Between 50 -55%";......."


    However if if the result is 49% i would prefer to have "46-53%" displayed

    Is this possible??
    Reply With QuoteRegister To Reply
  2. 01-29-2014, 11:47 AM #2
    etaf
    etaf is offline
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,743

    Re: Give a estimate based on a result

    you could try
    =(your formula)-0.04 & "% - "&(your formula)+0.04&"%"

    you may need to multiply by 100 to show %
    not sure what the result of
    ((E18+J18)-(E20+J20))/(E18+J18) produces
    i assume between 0 & 1
    if so you will need

    =((((E18+J18)-(E20+J20))/(E18+J18))-0.04)*100 & "% - "&((((E18+J18)-(E20+J20))/(E18+J18))+0.04)*100&"%"

    you could also use round() to avoid decimal places if required
    Last edited by etaf; 01-29-2014 at 11:49 AM.
    Wayne
    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
    Reply With QuoteRegister To Reply
  3. 01-29-2014, 11:49 AM #3
    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: Give a estimate based on a result

    Maybe something like this...

    =TEXT(FLOOR(A1,0.05),"0.00%")&" - "&TEXT(CEILING(A1,0.05),"0.00%")

    A
    B
    1
    47.50%
    45.00% - 50.00%
    2
    42.60%
    40.00% - 45.00%
    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
  4. 01-29-2014, 11:58 AM #4
    MarvinP
    MarvinP is offline
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,167

    Re: Give a estimate based on a result

    Hi iberg and welcome to the forum,

    Find the attached with two methods to do what you may want. The first is a pivot table that allows to show values in a Range. The second is using a lookup list. I hope you can use either of these.

    Look at http://www.contextures.com/xlPivot07.html for the pivot table answer
    Look at VLookup for the List answer.
    Attached Files Attached Files
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.
    Reply With QuoteRegister To Reply
  5. 01-29-2014, 12:10 PM #5
    ilberg
    ilberg is offline
    Registered User
    Join Date
    01-29-2014
    Location
    Aalborg, Denmark
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Give a estimate based on a result

    Thanks for quick responses to you all

    I have used the method AlKey proposed..

    Thanks again...
    Reply With QuoteRegister To Reply
  6. 01-29-2014, 12:15 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: Give a estimate based on a result

    You're welcome. Please don't forget to thank those who helped by clicking on Add Reputation * and mark thread as "Solved" if your issue has been resolved. (Selecting Thread Tools-> Mark thread as Solved).
    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. [SOLVED] Formula to give yes or no result based on cells data
    By twiztid in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-06-2014, 05:33 AM
  2. Point Estimate
    By smallexcel in forum Excel General
    Replies: 1
    Last Post: 08-12-2012, 03:48 PM
  3. VLOOKUP That needs to give result based on a ranking list
    By Sanecrazy in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-16-2012, 11:38 PM
  4. Row-based conditional search to give a cumulative result
    By harsh2209 in forum Excel General
    Replies: 2
    Last Post: 03-13-2010, 09:30 PM
  5. How do I get a cell to give a result based on the month we are in?
    By SHBmgr in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 07-17-2006, 05:55 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