formulas-"If" statement with more than 7 arguments

[Guest]

I am trying to complete an "IF" statement using more than 7 arguments but it
will not take. What do I do?

 

[Guest]

let's se what u got so far



"marcie" skrev:

 

[Guest]

=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))))))

 

[Earl Kiosterud]

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)

 

[Guest]

=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:

 

[Roger Govier]

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.