PDA

View Full Version : [SOLVED:] Tables everywhere


rcbricker
03-31-2005, 01:34 PM
Hiya guys here is an interesting one that will get more complicated as we go along.

I have a table (a matrix really) that I need to have a value gotten by comparing one value on the X axis to a value on the Y axis and having that value in the intersecting cell returned. lost yet? I will post and example. Once we have that worked out.

I need the values on the X and Y axis to be dependent on an outside value. Lets say from a second sheet (actually it will be a different workbook altogether).
The values will need to change based on an outside value that will be from 1-100. I have not figured out the results of what the change will be but figure I can define the changes after we figure out how to change them.:p
mdmackillop
03-31-2005, 02:24 PM
It sounds like you need a User Defined Function to compare the values. Something like the attached?
Zack Barresse
03-31-2005, 03:21 PM
Why a UDF Malcolm? Why not use native functions ...


=LOOKUP(($A2^2/B$1),{0,"Critical";4,"Yes";8,"No"})
mdmackillop
03-31-2005, 03:31 PM
I understood from reading it that there could also be another "outside" value, and felt that a UDF could give a greater flexibility, as I have no idea what the relationship is or the values involved.
rcbricker
03-31-2005, 03:44 PM
The matrix is to determine an action. If the X axis is 5 points higher than the Y the the return is yes. If the values are 5 or more less than the Y axis it returns Critical.

How the X and Y axis values change has not yet been determined just that one outside Value will cause the X to change and One different out side value will cause the Y to change. both values will be from 1 - 100.

There is a possibility that it won't need to change but i am going from the worst case senario. so a solution for changing the Axis values and a solution just to bring back a value using either the X or Y value.
Zack Barresse
03-31-2005, 04:15 PM
If you select B2:I10 and enter this formula ...


=IF(B$1+5<=$A2,"Critical",IF(B$1-5>=$A2,"Yes","No"))

.. and confirm with Ctrl + Enter, it should perform as you specify. Does it help?
rcbricker
03-31-2005, 04:20 PM
Worked like a charm thanks Fire I will post with more questions for this project as it grows!

Thanks everyone for trying
Zack Barresse
03-31-2005, 04:39 PM
You're welcome Rob. :) I'll mark it Solved then.