If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed.
a value | b value |marks section Value
x< 4.5 10 | x< 4.5 40 | 2.4 a 10
x> 4.5 20 | x> 4.5 60 | 4.9 a 20
---------------- |----------------| 5.5 b 60
Here's my problem, i've got 3 tables, the first two is for reference, and the third is what i am after.
The third table consists of 2 variables; marks and section, the corresponding "Value" depends on either table a or b AND the range in which the marks falls into. e.g. for the first row of the third table, refer to table a and 2.4<4.5 so the return value is 10.
My solution was to make a function on the first row of the third table:
=if(F2="a",if(E2<4.5,10,20),if(E2<4.5,40,60))
*note F2 and E2 refers to the column and row of the variables "section" and "marks"
This works fine for me but what if there are a lot of tables? say a to z? AND a range of values, say x<10,10<x<100,x>100..
I could use nested IF repeatedly but i think its too complicated and tedious to write. Is there any other way to get around this problem?
Yes that would be convenient if the data in the table keeps changing but what about additional table? If i had 13 more tables, that would take me a long time to write the IF function plus it would be difficult to check the codes.
Im thinking of changing the position of cell with respect to the table referred.
Say if table=a, then E2 value will be compared with cell $A$2
if table=b, then E2 value will be compared with cell $f$2
if table=c, then E2 value will be compared with cell $J$2
Any suggestions on how to do it?
* note that E2 from previous post:the first row of the third table. F2 and J2 are data from table a,b,c.
Linear Mode
