+ Reply to Thread
Results 1 to 5 of 5

Formula Question:use the format painter to drag this formula t

  1. #1
    Registered User
    Join Date
    07-29-2006
    Posts
    85

    Formula Question:use the format painter to drag this formula t

    I am trying to create a cell that will assess another cell and, depending on what value is in the cell, a point value will be given. I actually need the "Points" cell to assess more than one cell and that is where I am running into a problem.

    In other words, the "Points" cell is A8. If A1 is between 90% and 100% then 10 points should be added to A8. If A1 is between 80% and 89% then 8 points will be added to A8 and so forth. I also need cell A8 to populate with the same scale for other cells. So if it is 95% in A1, 86% in A3 and 91% in A5 then A8 should have 28 points. I will also need to use the format painter to drag this formula through 30 rows on several different sheets in the same workbook.

    Thanks for any help you can give me.

  2. #2
    Forum Expert swatsp0p's Avatar
    Join Date
    10-07-2004
    Location
    Kentucky, USA
    MS-Off Ver
    Excel 2010
    Posts
    1,545
    Set up a table with the various scores and values, then use the lookup in your table in a remote part of your sheet (or on another sheet):

    Col A Col B
    ..0-------0
    50-------2
    60-------4
    70-------6
    80-------8
    90------10

    and name this range tbl1 (or similar)

    Your formula in A8 will read:

    =SUM(VLOOKUP(A1,tbl1,2,1),VLOOKUP(A3,tbl1,2,1),VLOOKUP(A5,tbl1,2,1))

    with the result of 28.

    HTH
    Bruce
    The older I get, the better I used to be.
    USA

  3. #3
    Registered User
    Join Date
    07-29-2006
    Posts
    85
    I understand the philosophy behind this but I am just not sure how to execute. Currently I have nine sheets. One for each of our offices. I am going to add a tenth sheet and name it Point System.

    1. Is the table an insert or do I just create it on the grid?
    2. How do I name the table?
    3. In the formula I see that it reads (A1, tbl1, 2, 1). What is the 2 and the 1 referencing?

  4. #4
    Forum Expert swatsp0p's Avatar
    Join Date
    10-07-2004
    Location
    Kentucky, USA
    MS-Off Ver
    Excel 2010
    Posts
    1,545
    Create the table on a sheet (or in an area of your main sheet) that won't have rows or columns inserted or deleted that may destroy the data within the table.

    Highlight the entire data table, then select (from the main menu) Insert>Name>Define...

    in the "Names in workbook" line, enter the desired name for this range (e.g. tble1). Verify the "Refers to:" line matches your desired range and click OK


    From the Help section for Vlookup:

    Syntax

    VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

    Basically, the "2" says that after the lookup value is found, return the value in column 2 of the table. The "1" (or "True") means if an exact match is not found, the next largest value that is less than lookup_value is returned.

    Does this help you out?

  5. #5
    Registered User
    Join Date
    07-29-2006
    Posts
    85
    Yes! Perfect, Thanks a lot.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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