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.  Register To Reply

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  Register To Reply

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?  Register To Reply

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.  Register To Reply

5. Yes! Perfect, Thanks a lot.  Register To Reply