How to add equivalent values to excel data automatically?

Question

Example:
81-84 = 1.50
85-90 = 1.25
91-95 = 1.00

if a type 86 on the first column, an equivalent grade will be displayed on the next column

Answer 1

I would use VLOOKUP

Setup a table (let's use columns G and H)
- - -G- - - - - - -H
1- -0- - - - - - -<81
2- -81- - - - - -1.5
3- -85- - - - - -1.25
4- -91- - - - - -1
5- -96- - - - - ->95

Now if your value is in A1, you can put this formula in B1:
=VLOOKUP(A1,G1:H5,2)

If your value is within your ranges, the grades will be shown. As I wrote the table, it will show either <81 or >95 if the contents of A1 aren't within your range. If you wish, you can use Data Validation to limit the contents of A1 to any whole number between 81 and 95; then you wouldn't need the 1st and 5th rows from my table.

Answer 2

Make this table
Column1 (Say A) has the values 81, 85 and 91 (the startup ranges)
Column2 (Say B) has 84, 90 and 95 (the end ranges)
Column3 (Say C) has 1.5, 1.25 and 1.00
then in another cell (Say D3) type the number you want to search for (86)
Now, in the next cell paste this formula
=INDEX( C1:C4, MATCH( D3, A1:A4, 1))
I recommand you to add the maximum value (In your case 96 in the last cell of column A

mail me here in Y! Answers for more info

Read my profile, I am the XLMan

Answer 3

There are many ways to do it. If the ranges are subject to change, put them in a separate sheet and use vlookup() or hlookup() in a formula. If the ranges are static, just use a conditional statement like if() in a formula.

Answer 4

under is a VBA module which will do what you choose. First call cellular C1 "records" and make contact with cellular D1 "CumTotal" do no longer positioned any formula in cellular D1. Now press Alt-F11 to elevate the seen common window. in the challenge-VBA challenge window, double-click on the sheet call on your worksheet (no longer the call of the workbook, the call of the sheet). Then replica and paste the code under. the 1st line of the pasted code would be purple. replace "ByVa..." to "ByVal" and it may replace to black. each and every time C1 differences, the macro is immediately performed. It provides the value in C1 to the previous value in D1 and contraptions D1 to the hot finished. when you consider which you have named the cells, you need to use cells different than C1 and D1 for the enter values and cumulative finished. inner maximum Sub Worksheet_SelectionChange(ByVal aim As Excel.variety) Dim DataCell As variety Set DataCell = variety("records") If aim = DataCell Then variety("CumTotal").value = variety("CumTotal").value + variety("records").value end If end Sub you may digital mail me by way of Yahoo!solutions in case you have any questions.