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

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

"Jaye" wrote:

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

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.


"Jaye" wrote:

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

"Jaye" wrote in message
...
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

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



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!