formulas-"If" statement with more than 7 arguments

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to complete an "IF" statement using more than 7 arguments but it
will not take. What do I do?
 
=IF(D$14=7,EE20,IF(D$14=8,EE20+ET20,IF(D$14=9,EE20+ET20+FI20,IF(D$14=10,EE20+ET20+FI20+FX20,IF(D$14=11,EE20+ET20+FI20+FX20+GM20,IF(D$14=12,EE20+ET20+FI20+FX20+GM20+HB20))))))
 
Marcie,

You can nest more than 7 functions (like IF). But it won't work. In this
case, you should instead make a two-column table of your stuff, then use a
VLOOKUP in place of this IF-fest. Much more manageable.

The table:

7 =EE20
8 =EE20 +ET20
9 =EE20+ET20+FI20

=VLOOKUP(D14, list, 2, FALSE)
 
=IF(D$14=7,EE20,IF(D$14=8,EE20+ET20,IF(D$14=9,EE20+ET20+FI20,IF(D$14=10,EE20+ET20+FI20+FX20,IF(D$14=11,EE20+ET20+FI20+FX20+GM20,IF(D$14=12,EE20+ET20+FI20+FX20+GM20+HB20))))))+IF(D14=?;??20;IF(D14=??;??20))

Put a + after every 7'ens IF like i did in formula abowe

"marcie" skrev:
 
Hi Marcie

In the example shown, all of your column numbers on row 20 are
incrementing by 15 each time.
You don't say how far your series extends, but I am assuming that the
interval remains at 15 as you continue onward.

This being the case, you can achieve a simple solution without If
statements using Sumproduct
=SUMPRODUCT((EE20:IV20)*(MOD(COLUMN(EE:IV),15)=0)*(INT(COLUMN(EE:IV)/15)<=D$14))

You can restrict the range back from column IV to the maximum that your
range extends to.
 
Back
Top