7 + if statements

[Guest]

I am trying to create an excel workseet that will allocate operating expenses
to 6 different departments. On a previous tab ( Alloc Table) I have 10
allocation methods, each with a letter assigned A thru J. On my Expense tab,
in column E, I am assigning one of my allocation mehtods (by indicating one
of my letters A thru J) to each expense line. I am then using if stamtents
to choose the correct alloation rates and alloate the expenses. My formula is:

=IF($E7="A",($D7*'Alloc Table'!E$7),if($E7="B"($D7*'Alloc Table'!E$10),....
and on to "H".

However, my formula will not allow me to go beyond H ( 7 nested ) .

Can anyone give me suggestions as to how I can include more then 7 if
statemetns. As ypu can tell, I'm pretty basic here.

Thanks
Jay

 

[Guest]

Jaye,

The following should work if the remainder of your formula is consistent:

=$d7*offset('Alloc Table'!e$7,3*(code($e7)-65))

http://HelpExcel.com

 

[Guest]

Hi Jaye,
It is difficult to vision exeactly what you are trying to accomplish here,
but if your distribution is to six differant departments, does this mean you
are trying to allocate to six diff columns, each being a dept? If this is so,
you may be able to put your fomulas in each of those columns and test for
only the "A" to "H" associated with that department. In other words, don't
nest your if statements in one column, but in each column representing the
departments.

 

[Fredrik Wahlgren]

I am trying to create an excel workseet that will allocate operating expenses
to 6 different departments. On a previous tab ( Alloc Table) I have 10
allocation methods, each with a letter assigned A thru J. On my Expense tab,
in column E, I am assigning one of my allocation mehtods (by indicating one
of my letters A thru J) to each expense line. I am then using if stamtents
to choose the correct alloation rates and alloate the expenses. My formula is:

=IF($E7="A",($D7*'Alloc Table'!E$7),if($E7="B"($D7*'Alloc Table'!E$10),....
and on to "H".

However, my formula will not allow me to go beyond H ( 7 nested ) .

Can anyone give me suggestions as to how I can include more then 7 if
statemetns. As ypu can tell, I'm pretty basic here.

Thanks
Jay

If you really neeed more than 7 nested IF ststements, you need to create a
User Defined function in VBA.

/Fredrik

 

[Paul Black]

Hi Jaye,

Try this Site of Chip Pearsons, he has a Way of Overcoming the Nested
If's Rule.

Hope this Helps.
All the Best.
Paul