Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Chart - Require a Formula
I have the attached chart (no titles). I am trying to create a
formula to calculate the example that is listed below the chart, which is of course, based on the numbers in the chart. I would like to have it so that someone would type the 2 digit number on the left (listed in the example below the chart) and the next column would automatically give the user that typed the 2 digit number, the 1 digit number result (based on the chart). Ex: the user would type 41 and the result next to it would be 4 (the chart is for information only, it is the calculation underneath the chart that I require but it is based on numbers obtained from the chart). If anyone knows a formula, I would appreciate it, along with a brief explanation of the formula as I do not understand when it becomes really technical (I am a basic user). - 0 1 2 3 4 5 6 7 8 9 the dash is a blank cell 0 0 8 6 4 2 9 7 5 3 1 1 9 7 5 3 1 8 6 4 2 0 2 8 6 4 2 0 7 5 3 1 9 3 7 5 3 1 9 6 4 2 0 8 4 6 4 2 0 8 5 3 1 9 7 5 5 3 1 9 7 4 2 0 8 6 6 4 2 0 8 5 3 1 9 7 5 7 3 1 9 7 5 2 0 8 6 4 8 2 0 8 6 4 1 9 7 5 3 9 1 9 7 5 3 0 8 6 4 2 CHART works this way: Here is the number: 41619306 that we have. A person would input the 2 digit number on the left below (based on chart above, which they may or may not be able to see - doesn't matter). I want the number on the right (1 digit) to automatically appear based on the chart above. The 41 show that you start at the 4 on the vertical column and go across to the 1 on the horizontal column, which results in the 1 digit number 4, etc. Breakdown: 41 = 4 61 = 2 93 = 5 06 = 7 Thank you. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Chart - Require a Formula
wrote in message
ups.com... I have the attached chart (no titles). I am trying to create a formula to calculate the example that is listed below the chart, which is of course, based on the numbers in the chart. I would like to have it so that someone would type the 2 digit number on the left (listed in the example below the chart) and the next column would automatically give the user that typed the 2 digit number, the 1 digit number result (based on the chart). Ex: the user would type 41 and the result next to it would be 4 (the chart is for information only, it is the calculation underneath the chart that I require but it is based on numbers obtained from the chart). If anyone knows a formula, I would appreciate it, along with a brief explanation of the formula as I do not understand when it becomes really technical (I am a basic user). - 0 1 2 3 4 5 6 7 8 9 the dash is a blank cell 0 0 8 6 4 2 9 7 5 3 1 1 9 7 5 3 1 8 6 4 2 0 2 8 6 4 2 0 7 5 3 1 9 3 7 5 3 1 9 6 4 2 0 8 4 6 4 2 0 8 5 3 1 9 7 5 5 3 1 9 7 4 2 0 8 6 6 4 2 0 8 5 3 1 9 7 5 7 3 1 9 7 5 2 0 8 6 4 8 2 0 8 6 4 1 9 7 5 3 9 1 9 7 5 3 0 8 6 4 2 CHART works this way: Here is the number: 41619306 that we have. A person would input the 2 digit number on the left below (based on chart above, which they may or may not be able to see - doesn't matter). I want the number on the right (1 digit) to automatically appear based on the chart above. The 41 show that you start at the 4 on the vertical column and go across to the 1 on the horizontal column, which results in the 1 digit number 4, etc. Breakdown: 41 = 4 61 = 2 93 = 5 06 = 7 Thank you. You could do it without a formula using auto filter - 0 1 2 3 4 5 6 7 8 9 the dash is a blank cell V insert row and in cell under blank cell choose Data / 0 0 8 6 4 2 9 7 5 3 1 Filter / Autofilter Choose the row # and look at the 1 9 7 5 3 1 8 6 4 2 0 column # 2 8 6 4 2 0 7 5 3 1 9 3 7 5 3 1 9 6 4 2 0 8 4 6 4 2 0 8 5 3 1 9 7 5 5 3 1 9 7 4 2 0 8 6 6 4 2 0 8 5 3 1 9 7 5 7 3 1 9 7 5 2 0 8 6 4 8 2 0 8 6 4 1 9 7 5 3 9 1 9 7 5 3 0 8 6 4 2 Other wise use imagine lined up A B C D E F G H I J K L M N Excel row (excel column ##]) 01] - 0 1 2 3 4 5 6 7 8 9 02] 0 0 8 6 4 2 9 7 5 3 1 03] 1 9 7 5 3 1 8 6 4 2 0 Input: 04] 2 8 6 4 2 0 7 5 3 1 9 Row 4 05] 3 7 5 3 1 9 6 4 2 0 8 Column 1 06] 4 6 4 2 0 8 5 3 1 9 7 07] 5 5 3 1 9 7 4 2 0 8 6 08] 6 4 2 0 8 5 3 1 9 7 5 09] 7 3 1 9 7 5 2 0 8 6 4 10] 8 2 0 8 6 4 1 9 7 5 3 11] 9 1 9 7 5 3 0 8 6 4 2 =OFFSET(A1,MATCH(N4,$A$2:$A$11,0),MATCH(N5,$B$1:$K $1,0)) OFFSET = A1 because it's blank MATCH N4=Row to lookup in range A2:A11 N5=Column to lookup in range B1:K1 0 indicates exact match |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Chart - Require a Formula
"PBalmanno" wrote in message
... wrote in message ups.com... I have the attached chart (no titles). I am trying to create a formula to calculate the example that is listed below the chart, which is of course, based on the numbers in the chart. I would like to have it so that someone would type the 2 digit number on the left (listed in the example below the chart) and the next column would automatically give the user that typed the 2 digit number, the 1 digit number result (based on the chart). Ex: the user would type 41 and the result next to it would be 4 (the chart is for information only, it is the calculation underneath the chart that I require but it is based on numbers obtained from the chart). If anyone knows a formula, I would appreciate it, along with a brief explanation of the formula as I do not understand when it becomes really technical (I am a basic user). - 0 1 2 3 4 5 6 7 8 9 the dash is a blank cell 0 0 8 6 4 2 9 7 5 3 1 1 9 7 5 3 1 8 6 4 2 0 2 8 6 4 2 0 7 5 3 1 9 3 7 5 3 1 9 6 4 2 0 8 4 6 4 2 0 8 5 3 1 9 7 5 5 3 1 9 7 4 2 0 8 6 6 4 2 0 8 5 3 1 9 7 5 7 3 1 9 7 5 2 0 8 6 4 8 2 0 8 6 4 1 9 7 5 3 9 1 9 7 5 3 0 8 6 4 2 CHART works this way: Here is the number: 41619306 that we have. A person would input the 2 digit number on the left below (based on chart above, which they may or may not be able to see - doesn't matter). I want the number on the right (1 digit) to automatically appear based on the chart above. The 41 show that you start at the 4 on the vertical column and go across to the 1 on the horizontal column, which results in the 1 digit number 4, etc. Breakdown: 41 = 4 61 = 2 93 = 5 06 = 7 Thank you. You could do it without a formula using auto filter - 0 1 2 3 4 5 6 7 8 9 the dash is a blank cell V insert row and in cell under blank cell choose Data / 0 0 8 6 4 2 9 7 5 3 1 Filter / Autofilter Choose the row # and look at the 1 9 7 5 3 1 8 6 4 2 0 column # 2 8 6 4 2 0 7 5 3 1 9 3 7 5 3 1 9 6 4 2 0 8 4 6 4 2 0 8 5 3 1 9 7 5 5 3 1 9 7 4 2 0 8 6 6 4 2 0 8 5 3 1 9 7 5 7 3 1 9 7 5 2 0 8 6 4 8 2 0 8 6 4 1 9 7 5 3 9 1 9 7 5 3 0 8 6 4 2 Other wise use imagine lined up A B C D E F G H I J K L M N Excel row (excel column ##]) 01] - 0 1 2 3 4 5 6 7 8 9 02] 0 0 8 6 4 2 9 7 5 3 1 03] 1 9 7 5 3 1 8 6 4 2 0 Input: 04] 2 8 6 4 2 0 7 5 3 1 9 Row 4 05] 3 7 5 3 1 9 6 4 2 0 8 Column 1 06] 4 6 4 2 0 8 5 3 1 9 7 07] 5 5 3 1 9 7 4 2 0 8 6 08] 6 4 2 0 8 5 3 1 9 7 5 09] 7 3 1 9 7 5 2 0 8 6 4 10] 8 2 0 8 6 4 1 9 7 5 3 11] 9 1 9 7 5 3 0 8 6 4 2 =OFFSET(A1,MATCH(N4,$A$2:$A$11,0),MATCH(N5,$B$1:$K $1,0)) OFFSET = A1 because it's blank MATCH N4=Row to lookup in range A2:A11 N5=Column to lookup in range B1:K1 0 indicates exact match I forgot to say put the formula in N6. Once you've got it running you can move things around. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Which Excel Functions Require Arrays to be Sorted? | Excel Worksheet Functions | |||
do you require up dates for office excel 2003 | Excel Discussion (Misc queries) | |||
IS THERE A WAY TO HAVE CELLS IN AN EXCEL SPREADSHEET REQUIRE DATA? | Excel Worksheet Functions | |||
trying to create a formula and require help. | Excel Worksheet Functions | |||
Require Tmplwiz.exe for Excel 2003 | Excel Discussion (Misc queries) |