Forum Discussion
MULTIPLE IF CONDTITIONS OVER 64
Greetings! I have a formula with more than 64 nested items and I need to simplify it so that I can reference a labor matrix. =IF(B2=0.1,Gas!C35,IF(B2=0.2,Gas!D35,IF(B2=0.3,Gas!E35,IF(B2=0.4,G...
I didn't look at every single item but it sure looks like an INDEX should work here something like:
=INDEX(Gas!A34:K50, INT(B2)+1, MOD(B2,1)+1)
EDIT: I added 1 to the row and cols and changed the ref range to accommodate the 0 cases
- How do I make it populate into C2 the correct value from the grid. Example, in B2 I want to enter 2.3 and it pull the value from E37 or 2.3. B2 will be a cell that is manually entered labor hours, 1, 2.2, 3.3, etc.
Thank you for your assistance!- From what I see, you want a value in B2 to define the location in the grid such that the integer portion indicates the row and the decimal portion is the column. so the formula:
=INDEX(Gas!A34:K50, INT(B2)+1, MOD(B2,1)+1)
says to start at cell A34 and then move down the integer portion of B2 +1 rows and to the right the decimal portion of B2 +1 columns.
so in you example the value 2.3 would move down 3 rows (2+1) and to the right 4 columns (3+1) so starting at A34 would go down to row 37 and to the right to Col E and return the value in cell E37.
