+ Reply to Thread
Results 1 to 4 of 4

need help with cell formatting

  1. #1
    Registered User
    Join Date
    01-07-2016
    Location
    oregon
    MS-Off Ver
    professional plus 2010
    Posts
    1

    need help with cell formatting

    here is what I want to do.....I want to type a percentage into a1 I want b1 to have about 6 different range values and if a1 is in between a certain range it puts a predetermined number in c1

    example

    A1
    a percentage number example 17%

    B1
    should have ranges like if A1 is between 16%-20% the value is 2 if a1 is between 21%-25% the value is 3 and so on

    C1
    should contain the predertimined value given to the ranges put into b1

    anyone that can help me with this is very much appreciated

  2. #2
    Forum Contributor
    Join Date
    10-29-2014
    Location
    udaipur, rajasthan
    MS-Off Ver
    2007
    Posts
    352

    Re: need help with cell formatting

    hi welcome in forum,

    suggest to you please upload a sample file with expected result , for attachments "GO Advance" then "Manage Attachments" and upload a sample workbook.
    If answer helped you say Thanks by Add Reputation

  3. #3
    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
    52,917

    Re: need help with cell formatting

    Hi, welcome to the forum

    I want b1 to have about 6 different range values
    Thats not the way it works. You need to put each level in it's own cell - and that level cannot be a range, either, put the lowest value in, along with it's corresponding value - then use vlookup or index/match
    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

  4. #4
    Valued Forum Contributor
    Join Date
    12-28-2014
    Location
    NSW, Australia
    MS-Off Ver
    MS365
    Posts
    604

    Re: need help with cell formatting

    I think a nested IF will do what you want, although a vlookup is much easier to follow and to change/update later.

    An IF statement has 3 sections to it. Question/condition, operation if true, operation if false.
    Something like =IF(A1=1,"Yes","NO") will place YES in the cell if a 1 is in A1 otherwise it will place NO in the cell.
    With a nested IF, its exactly the same except the NO or false part asks another question.
    The thing that tricks some people up, is that you need to make sure the 1st question can't rule out any other.
    e.g. =IF(A1>25%,1,IF(A1>55%,2,IF(A1>75%,3,4)))
    That looks good if you're not following the rules. Every answer above 25% will =1. It does say that if A1>75% then 3....but it will never get to ask that question. The 1st question asks if A1>25%...well 75% is > 25% so its a 1...formula ends.
    So start the questions with the 1st that can be ruled out and work down.

    =IF(A1>75%,4,IF(A1>50%,3,IF(A1>25%,2,1)))

    That has 4 ranges: 0-25% = 1, 26-50% = 2, 51-75%=3, 76% and above=4

    I hope you didn't mind the lesson there thought some people searching might find it helpful.
    And I hope I understood your question properly and it works for you.

    Although this skips B1, that formula goes directly into C1 and B1 can be empty.

+ 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. Replies: 3
    Last Post: 11-04-2013, 12:47 AM
  2. Replies: 1
    Last Post: 08-28-2013, 09:07 AM
  3. Conditional Formatting via VBA: Change formatting in range based on value of each cell
    By ralphjmedia in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-12-2013, 10:37 AM
  4. Delete Conditional Formatting conditions but keep cell formatting - Excel 2010
    By tetreama in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-09-2012, 08:28 PM
  5. Replies: 1
    Last Post: 03-05-2012, 06:20 PM
  6. Macro for formatting fonts and cell colours - not Conditional Formatting
    By Kayaness in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-07-2011, 03:46 AM
  7. Replies: 2
    Last Post: 11-02-2007, 12:03 PM
  8. Replies: 4
    Last Post: 07-18-2007, 05:39 PM

Tags for this Thread

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