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

1. ## 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.

2. 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)

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

with the result of 28.

HTH

3. 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. 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.

5. Yes! Perfect, Thanks a lot.

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

#### 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