I need to create a formula in excel.
I have 10 categories of numbers that are based on letters D through L
vertical, and 8 symbols horizontally. THESE NUMBERS AND FIGURES FOR
THE 8 CATEGORIES HAVE ALREADY BEEN ENTERED ALREADY.
category 1 if the item is ANYWHERE FROM to .30-.37
ITEM 1 ITEM 2 ITEM 3 ETC. ITEM 8
D
E
F
G
H
I
J
K
L
category 2 if the item is EQUAL ANYWHERE FROM to.38-.45
ITEM 1 ITEM 2 ITEM 3 ETC. ITEM 8
D 60 -->50<-- ETC ETC
E 62
F 63
G 59
H 56
I ETC
J
K
L
NEXT, I will be entering in 3 variables in another section. The first
number is to do with the size, FROM .3 TO 1.5, the second is the
letter, either D-L, and the final is whether it is Item 1-8.
BASED ON WHAT I ENTER I NEED A FORMULA TO SAY "IF CELL B2 IS D, AND
CELL C2 IS ITEM 2 AND CELL A2 FALLS IN CATEGORY 1, THEN APPLY THE FORMULA THAT
WILL= (The SIZE entered [A2] * THE REFERENCED NUMBER {IE. D, ITEM 2 IN
CATEGORY 2 * 70)
BUT....IF THE ITEM IS D, ITEM 2, BUT IN CATEGORY 6 AS OPPOSED TO
CATEGORY 2, THE ABOVE FORMULA IS IN ESSENCE FALSE AND A NEW FORMULA
WOULD HAVE TO APPLY, OR IF IT WASN'T D, ETC.
I AM NOT SURE IF 7200 FORMULAS NEED TO BE DONE (WHICH MAY
HAVE NEED TO BE STRUNG TOGETHER SOMEHOW) , OR IF ARGUMENTS CAN BE
ENTERED THAT FILTERS OUT RESULTS,
IF THE FORMULAS NEED TO BE STRUNG TOGETHER, I NEED
TO KNOW HOW TO DO IT PROPERLY WHILE KEEPING THE CELL REFERENCES IN PROPER
SHAPE SO THAT THE REFERENCES CARRY THROUGH.
I BELIEVE THAT MY INITIAL FORMULA IS NOT CORRECT FOR DOING MULTIPLE
FORMULAS NOR FOR CARRYING THROUGH CELL REFERENCES. SEE BELOW
=SUM(AND(((B2="D")),((C2="ITEM2")),(AND(A2<0.38,A2>0.29)))*$G$2*A2*0.7)
<--LAST PART IS THE REFERENCED NUMBER
i HAVE CREATED FORMULAS JUST LIKE THE ONE ABOVE THAT ALL POINT TO A2
AS THE BEGINNING CELL SO THAT IF I HAD TO STRING TOGETHER A FORMULA,
THEN IT WOULD 'HOPEFULLY' MOVE DOWN TO APPLY TO A3, A4, ETC. IF
RESTRINGING IS REQUIRED, I WILL NEED TO MAKE SURE THAT THIS CARRIES
THROUGH SO THAT I DON'T SPEND TIME CHANGING CELL REFERENCES. |
Request for Question Clarification by
answerguru-ga
on
16 Nov 2003 19:53 PST
Hi there!
I can certainly code this formula for you, however just to eliminate
any inconsistency in my code and the tables that you have already
defined (for each category) it would be better if you could post your
excel file somewhere for me to download. Otherwise, I could develop it
in a generic fashion so that you could go and make the appropriate
changes after I deliver the formula.
At any rate, I have already started with an outline of what needs to
be done and I'm fairly confident this can be achieved :)
answerguru-ga
|
Clarification of Question by
wenanator-ga
on
17 Nov 2003 14:05 PST
A generic outline would be best. Sopmetimes ranges need to be
changed, and sometimes the columns and the other items change as well.
If you need anything from me let me know.
thanks,
|
Request for Question Clarification by
answerguru-ga
on
18 Nov 2003 07:59 PST
Just a quick update for you - I have started on this function and
estimate that it will be complete by tomorrow evening at the latest.
Is this acceptable?
answerguru-ga
|
Clarification of Question by
wenanator-ga
on
18 Nov 2003 14:10 PST
Not a problem. Better to do the job right the first time so that we
don't have to go back and forth and that time isn't wasted if there
are problems.
|
Hi wenenator-ga,
I have completed the formula for you as requested - I will just give
you a brief insight into my process so that you can mimick it on your
side when setting thing up:
1. Create an excel file with 10 worksheets
2. Each worksheet contains a grid as you defined in your question
within the range A1:I10 (this is necessary as the code is currently
written)
3. Opened the VB Editor from Excel (Alt+F11)
4. Created a new module (Module1) by right-clicking on the VBAProject
for the current file and Insert > Module
5. Within the module I entered the following code:
'BEGIN CODE
Function CategoryLookup(value As Double, letter As String, item As
Integer) As Double
' Defining required variables
Dim activeSheet As Worksheet
Dim sheetIndex As Integer
Dim rowVal As Integer
Dim colVal As Integer
' Determine category sheet based on value argument
If value >= 0.3 And value <= 0.37 Then
sheetIndex = 1
ElseIf value >= 0.38 And value <= 0.45 Then
sheetIndex = 2
ElseIf value >= 0.46 And value <= 0.53 Then
sheetIndex = 3
ElseIf value >= 0.54 And value <= 0.61 Then
sheetIndex = 4
ElseIf value >= 0.62 And value <= 0.69 Then
sheetIndex = 5
ElseIf value >= 0.7 And value <= 0.77 Then
sheetIndex = 6
ElseIf value >= 0.78 And value <= 0.85 Then
sheetIndex = 7
ElseIf value >= 0.86 And value <= 0.93 Then
sheetIndex = 8
ElseIf value >= 0.94 And value <= 1.01 Then
sheetIndex = 9
ElseIf value >= 1.02 And value <= 1.09 Then
sheetIndex = 10
ElseIf value >= 1.1 And value <= 1.17 Then
sheetIndex = 11
Else
sheetIndex = -1 ' value could not be found - error flag
End If
'quit if value given is not in defined range
If sheetIndex = -1 Then
CategoryLookup = -1 'return error and quit
End If
'resolve row based on letter provided
If letter = "D" Then
rowVal = 2
ElseIf letter = "E" Then
rowVal = 3
ElseIf letter = "F" Then
rowVal = 4
ElseIf letter = "G" Then
rowVal = 5
ElseIf letter = "H" Then
rowVal = 6
ElseIf letter = "I" Then
rowVal = 7
ElseIf letter = "J" Then
rowVal = 8
ElseIf letter = "K" Then
rowVal = 9
ElseIf letter = "L" Then
rowVal = 10
Else
rowVal = -1 'invalid letter entered
End If
'resolve row based on letter provided
If item >= 1 And item <= 8 Then
colVal = item + 1
Else
colVal = -1 'invalid item number entered
End If
'quit if row/col values cannot be determined
If rowVal = -1 Or colVal = -1 Then
CategoryLookup = -1 'return error and quit
Else
'retrieve appropriate value and return it
CategoryLookup = ActiveWorkbook.Sheets(sheetIndex).Cells(rowVal, colVal)
End If
End Function
'END CODE
After saving you should be able to access this function like any other
worksheet function by calling it from a cell within the workbook.
For example, the function call:
=CategoryLookup(0.40, "F", 4)
will return the value corresponding to Item 4, letter F in the sheet
for the category representing values containing 0.40.
In general, providing arguments to the function that are invalid will
result in a return value of -1. I assume this value will never occur
within any of your category tables.
I have tried to strategically place comments in the code so you know
where certain things are occuring - if any of it doesn't make sense
please post a clarification and I'll be happy to help :)
Cheers!
answerguru-ga |
Clarification of Answer by
answerguru-ga
on
20 Nov 2003 00:19 PST
Just wanted to also point out that you had mentioned your input values
ranged from 0.3 to 1.5, however the pattern you provided only resulted
in values up to 1.17 being handled. You can change the ranges yourself
as appropriate :)
answerguru-ga
|
Request for Answer Clarification by
wenanator-ga
on
20 Nov 2003 13:50 PST
Hi Answer: Need a 2 1/2 Part Clarification Please
I'm not able to get it yet. When I said that something was Item 1,
Item 2, etc,.. that is an operator, a figurative reference,(i believe)
similar to D through L. For the moment, Lets use AB as item 1, AC as
Item 2, AD, etc for ease. One thing that I think may be preventing
the full formula is that as the rest of the formula says if D, then...
if E, etc, I don't see anything alloted for if column (or row or
whatever) is AB, then..., if AC, then...
I hope that you understand what I am saying with this.
I am also having a bit of dificulty understanding how to properly
reference the formula. When you say:
"After saving you should be able to access this function like any other
worksheet function by calling it from a cell within the workbook.
For example, the function call:
=CategoryLookup(0.40, "F", 4)
will return the value corresponding to Item 4, letter F in the sheet
for the category representing values containing 0.40."
I know nothing about VB so I don't know how to reference any formulas
that are created. All i know is to do a basic Excel reference like
=[Formula.xls]Sheet1:Sheet8'!$A$1:$I$10
Should I be putting in my entries on the same file, or does it matter?
|
Clarification of Answer by
answerguru-ga
on
20 Nov 2003 23:46 PST
Hi wenenator-ga,
In response to your clarifications:
The reason I did not include a set of if statements for the columns is
because the structure you intially provided was Item 1, Item 2, etc. I
was able to use the number and add one to it in this section:
If item >= 1 And item <= 8 Then
colVal = item + 1
Else
colVal = -1 'invalid item number entered
End If
Regarding the use of the formula, it is quite simple and very much
like any excel worksheet function that is built into the program. For
example, I'm sure you're familiar with Excel's SUM function, which you
can use like this:
=SUM(A1:A20)
This information is entered directly into any worksheet cell and adds
the values in the provided range. Similarly, you can use this function
(once it has been saved in a Module) by typing the following formula
into any cell in the workbook:
=CategoryLookup(0.40, "F", 4)
This has nothing to do with VBA from the user's point of view - just
assume you are using a regular formula (that is how the custom formula
has been designed in Excel).
Regarding your question about "entries", I'm not quite sure what you
are referring to - do you mean your category tables or the place where
you actually use the formula?
answerguru-ga
|
Request for Answer Clarification by
wenanator-ga
on
21 Nov 2003 00:30 PST
Hi Guru:
I would definitely need the "if" statements for the 'item' columns, as
mentioned previously, sometimes the names of the column items change
and so there isn't necessarily a static number system.
With respect to the category lookup, I think I get it now. Basically
what is happening is that based on the numbers provided it is going to
return a value using the parameters in the other sheet. It is THEN
that I need to create whatever formula that applies to the number that
is referenced. Got it.
Almost there Guru. Thanks for the patience, and if you could just
revamp the Item Columns into a generic "if" form like the others then
I think that it should work out.
|
Clarification of Answer by
answerguru-ga
on
21 Nov 2003 05:44 PST
Actually, the way it currently works, the column lookup has nothing to
do with the label of the column, so you can safely change that without
affecting the formula. It is actually the order of the columns that
matters here. For example, given one of your example tables:
category 2 if the item is EQUAL ANYWHERE FROM to.38-.45
ITEM 1 ITEM 2 ITEM 3 ETC. ITEM 8
D 60 -->50<-- ETC ETC
E 62
F 63
G 59
H 56
I ETC
J
K
L
The first column containing values is column two, therefore if the formula is:
=CategoryLookup(0.40, "F", 1)
Then we know the return value 63. You could equivalently change all
the labels of the columns as follows:
category 2 if the item is EQUAL ANYWHERE FROM to.38-.45
AB AC AD ETC. AX
D 60 -->50<-- ETC ETC
E 62
F 63
G 59
H 56
I ETC
J
K
L
Even under this scheme, the same formula:
=CategoryLookup(0.40, "F", 1)
would result in a value of 63.
Therefore you don't really need to change the code - do you see how it works now?
answerguru-ga
|
Request for Answer Clarification by
wenanator-ga
on
21 Nov 2003 17:03 PST
Hi Answer:
Unfortunately it isn't going too smoothly. As mentioned above, I need
to be able to do the following:
BASED ON WHAT I ENTER I NEED A FORMULA TO SAY "IF CELL B2 IS D, AND
CELL C2 IS ITEM 2 AND CELL A2 FALLS IN CATEGORY 1, THEN APPLY THE FORMULA THAT
WILL= (The SIZE entered [A2] * THE REFERENCED [looked up] NUMBER {IE.
D, ITEM 2 IN CATEGORY 2 * 70)
Although I'm sure that you've seen it before, I am just going to put
the exact two sheets that I am working with.
We will call this Sheet 1 and the title of the document is simply FORMULA
with the range applying only to .30-.39
COLUMNS
A B C D E F G H I
AB AC AD AE AF AG AH AI
D 46 40 35 32 29 22 18 13
E 41 37 33 30 27 21 17 12
F 38 35 30 28 25 20 16 11
G 36 32 28 25 22 18 15 10
H 30 26 22 20 18 16 14 9
I 23 20 19 18 16 15 13 9
J 18 17 16 15 14 13 12 8
K 16 15 14 13 12 11 10 8
L 13 12 12 11 11 10 9 6
SHEET 2 FOR ITEMS WITHIN THE RANGE OF .40-.47
COLUMNS
A B C D E F G H I
AB AC AD AE AF AG AH AI
D 75 60 54 48 44 38 31 20
E 60 54 50 46 43 36 29 19
F 54 50 47 43 39 33 27 18
G 50 45 42 39 34 29 24 16
H 44 39 35 33 29 26 21 15
I 35 31 28 26 24 22 20 14
J 27 25 24 22 21 20 19 13
K 23 22 21 19 18 17 16 12
L 21 20 20 18 18 17 15 10
IN A SEPEARATE FILE (<-I GATHER) I WILL BE ENTERING IN ITEMS SUCH AS THE FOLLOWING:
LENGTH LETTER SYMBOL FORMULA?
0.30 H AG ???
0.30 J AF ?
0.40 I AF
0.45 H AF
0.46 I AG
0.49 G AG
0.51 I AF
0.54 I AF
0.61 H AG
0.61 I AF
0.71 I AF
0.81 H AG
0.88 H AF
0.92 I AG
1.06 G AH
1.10 I AH
1.15 I AG
1.26 H AF
1.36 I AF
in the above example, the first thing that should happen in the
formula is that based on COLUMN A being .30,COLUMN B BEING H,& COLUMN
C BEING AG it should use sheet 1 of the 'formula' document, and based
on it matching 'H' & 'AG' a value of 16 should be spat out, THE SECOND
ROW WOULD EQUAL 14, THE THIRD WOULD GO TO SHEET 2, using the same
column/row parameters, THUS IT WOULD BE 24. i can handle the rest
from there, but so far I haven't gotten anything near that and have
only managed to get a REF#. Also, one area where I think I may be
going off track is the formula script that you gave me. I am not sure
if I am supposed to make any changes to it like entering in the
workbook name, sheet number, etc, etc..and if so, what/how changes
need to be made.
If this is the case, please let me know somehow.
So, in the area where it says formula, I need the EXACT formula that I
should enter with the exact reference area, as well as if changes need
to be made, which ones.
I can't create a query for each operation or one cell that says,
.30,f,ag it needs to be based on column a, then b, then c.
|
Clarification of Answer by
answerguru-ga
on
21 Nov 2003 18:53 PST
Hi sgain,
I wish you had given me this information earlier - the reason the
function will not accept that input is because it is expecting a
numeric value in the last parameter. I will fix the code and have the
result for you later tonight.
answerguru-ga
|
Clarification of Answer by
answerguru-ga
on
21 Nov 2003 18:59 PST
I also wanted to point out that since you are supposed to be placing
the code into a Module, you will need to use the formulas in the same
workbook as where the tables of values are located. Therefore if you
have 10 categories, Sheet1 through Sheet10 will hold data in tables,
and then you can use the formula within that same book in any sheet
number higher than 10.
answerguru-ga
|
Clarification of Answer by
answerguru-ga
on
21 Nov 2003 19:22 PST
OK, here is the updated code according to the sample you have provided:
'BEGIN CODE
Function CategoryLookup(value As Double, letter As String, symbol As
String) As Double
' Defining required variables
Dim activeSheet As Worksheet
Dim sheetIndex As Integer
Dim rowVal As Integer
Dim colVal As Integer
' Determine category sheet based on value argument
If value >= 0.3 And value <= 0.37 Then
sheetIndex = 1
ElseIf value >= 0.38 And value <= 0.45 Then
sheetIndex = 2
ElseIf value >= 0.46 And value <= 0.53 Then
sheetIndex = 3
ElseIf value >= 0.54 And value <= 0.61 Then
sheetIndex = 4
ElseIf value >= 0.62 And value <= 0.69 Then
sheetIndex = 5
ElseIf value >= 0.7 And value <= 0.77 Then
sheetIndex = 6
ElseIf value >= 0.78 And value <= 0.85 Then
sheetIndex = 7
ElseIf value >= 0.86 And value <= 0.93 Then
sheetIndex = 8
ElseIf value >= 0.94 And value <= 1.01 Then
sheetIndex = 9
ElseIf value >= 1.02 And value <= 1.09 Then
sheetIndex = 10
ElseIf value >= 1.1 And value <= 1.17 Then
sheetIndex = 11
Else
CategoryLookup = -1 'return error and quit
End If
If letter = "D" Then
rowVal = 2
ElseIf letter = "E" Then
rowVal = 3
ElseIf letter = "F" Then
rowVal = 4
ElseIf letter = "G" Then
rowVal = 5
ElseIf letter = "H" Then
rowVal = 6
ElseIf letter = "I" Then
rowVal = 7
ElseIf letter = "J" Then
rowVal = 8
ElseIf letter = "K" Then
rowVal = 9
ElseIf letter = "L" Then
rowVal = 10
Else
rowVal = -1 'invalid letter entered
End If
If symbol = "AB" Then
colVal = 2
ElseIf symbol = "AC" Then
colVal = 3
ElseIf symbol = "AD" Then
colVal = 4
ElseIf symbol = "AE" Then
colVal = 5
ElseIf symbol = "AF" Then
colVal = 6
ElseIf symbol = "AG" Then
colVal = 7
ElseIf symbol = "AH" Then
colVal = 8
ElseIf symbol = "AI" Then
colVal = 9
Else
colVal = -1 'invalid item number entered
End If
If rowVal = -1 Or colVal = -1 Then
CategoryLookup = -1 'return error and quit
Else
CategoryLookup = ActiveWorkbook.Sheets(sheetIndex).Cells(rowVal, colVal)
End If
End Function
'END CODE
Make sure that you replace the old version of the code with this new
one. Now you should be able to use the formula as follows:
=CategoryLookup(0.30, "H", "AG")
Equivalently, you can also just put cell references corresponding to
these values into the formula. In the example you provided earlier,
let's assume that the first row of data are in cells A2, B2, and C2
(length, letter, symbol). Then we can call the formula as follows:
=CategoryLookup(A2,B2,C2)
I have tested this thoroughly and you should not have any problems with it.
answerguru-ga
|
