tables and if function

**eddy1234** · 07-03-2009, 11:20 AM

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?

**zbor** · 07-03-2009, 04:21 PM

Then you need not to refer to numbers but to cells (where you can change numbers)

For example: instead of =if(F2="a",if(E2<4.5,10,20),if(E2<4.5,40,60))

Define some cells eg A1=4.5, A2=10, A3=20, A4=40, C50=60 and write:

=if(F2="a",if(E2<$A$1,$A$2,$A$3),if(E2<$A$1,$A$4,$C$50))

**eddy1234** · 07-03-2009, 09:01 PM

Hi Zbor,

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.