One Nest Too Many - Help!

  • Thread starter Thread starter SamuelT
  • Start date Start date
S

SamuelT

Hi all,

I'm working on a spreadsheet and have hit upon a problem. Due to an
extension in the range I am calculating my nested formula now exceeds
the magical 7 limit. I only actually need to add on more range to
calculate, but cannot work out what to do.

The formula I'm using is below. I basically just need to do the same
calculation for columns BT and BU:

=IF('Resource Calcs'!B2="","",IF('Resource Calcs'!$BF2='Resource By
Div'!$A$1,'Resource Calcs'!B2*'Resource Calcs'!$BG2,IF('Resource
Calcs'!$BH2='Resource By Div'!$A$1,'Resource Calcs'!B2*'Resource
Calcs'!$BI2,IF('Resource Calcs'!$BJ2='Resource By Div'!$A$1,'Resource
Calcs'!B2*'Resource Calcs'!$BK2,IF('Resource Calcs'!$BL2='Resource By
Div'!$A$1,'Resource Calcs'!B2*'Resource Calcs'!$BM2,IF('Resource
Calcs'!$BN2='Resource By Div'!$A$1,'Resource Calcs'!B2*'Resource
Calcs'!$BO2,IF('Resource Calcs'!$BP2='Resource By Div'!$A$1,'Resource
Calcs'!B2*'Resource Calcs'!$BQ2, IF('Resource Calcs'!$BR2='Resource By
Div'!$A$1,'Resource Calcs'!B2*'Resource Calcs'!$BS2, 0))))))))

I've been advised that I could split the formula across two cells, the
only problem being that I then copy the formula across a massive range
of around 2000 cells, and it will take quite some time to add new rows
and columns...looking for a simpler solution! :)

Any ideas?

Thanks!

SamuelT
 
I do not have time to check this but here is any idea. The formula below
does not test B2 if is empty.This removes one IF giving room for another. If
B2 is blank you will get zero. Format the cell to display a blank when the
result is zero.

=IF('Resource Calcs'!$BF2='Resource By
Div'!$A$1,'Resource Calcs'!B2*'Resource Calcs'!$BG2,IF('Resource
Calcs'!$BH2='Resource By Div'!$A$1,'Resource Calcs'!B2*'Resource
Calcs'!$BI2,IF('Resource Calcs'!$BJ2='Resource By Div'!$A$1,'Resource
Calcs'!B2*'Resource Calcs'!$BK2,IF('Resource Calcs'!$BL2='Resource By
Div'!$A$1,'Resource Calcs'!B2*'Resource Calcs'!$BM2,IF('Resource
Calcs'!$BN2='Resource By Div'!$A$1,'Resource Calcs'!B2*'Resource
Calcs'!$BO2,IF('Resource Calcs'!$BP2='Resource By Div'!$A$1,'Resource
Calcs'!B2*'Resource Calcs'!$BQ2, IF('Resource Calcs'!$BR2='Resource By
Div'!$A$1,'Resource Calcs'!B2*'Resource Calcs'!$BS2, 0)))))))
Now you have 6 nested in the second IF and should be able to add another.
best wishes
 
Hi SamuelT,

You can try the following ARRAY formula (confirm with Ctrl+Shift+Enter, not
just Enter) which basically eliminates the nesting issue totally and you
should be able to copy it easily:

=IF('Resource Calcs'!$B2="","",'Resource
Calcs'!$B2*INDEX(IF(MOD(COLUMN('Resource
Calcs'!$BF2:$BS2),2)=MOD(COLUMN(INDEX('Resource
Calcs'!$BF2:$BS2,2)),2),'Resource Calcs'!$BF2:$BS2),MATCH('Resource By
Div'!$A$1,IF(MOD(COLUMN('Resource
Calcs'!$BF2:$BS2),2)=MOD(COLUMN(INDEX('Resource
Calcs'!$BF2:$BS2,1)),2),'Resource Calcs'!$BF2:$BS2),0)+1))

Notes:
1) this is a relatively expensive formula and may slow your sistem down
significantly if copied to too many cells.
2) the good news is that it doesn't seem to be volatile, so it will only
recalculate if the affected ranges change

Regards,
KL
 
in such case ,it is preferable to do programming in VB code,back side


that will help run programme more smooth..
 
"In such case" it is bulex! What exactly is the advantage of using VBA in
this case particular circumstance appart from:

1) slower execution
2) need to ensure the user enables macros upon openning the file
3) ensure that user has sufficient rights to set security level to Medium
4) less flexibility
5) higher risk of error

Remember: the macro will need to be executed each time the involved ranges
change (Change or/and Calculate event of the Sheet/Sheets will probably need
to be used), that is already taken care of by the formula and since it is
not volatile it won't recalculate on every recalculation in the workbook.

You try it in VBA and see if it is better ;-)

Regards,
KL
 
KL wrote...
You can try the following ARRAY formula (confirm with Ctrl+Shift+Enter, not
just Enter) which basically eliminates the nesting issue totally and you
should be able to copy it easily:

=IF('Resource Calcs'!$B2="","",'Resource Calcs'!$B2
*INDEX(IF(MOD(COLUMN('Resource Calcs'!$BF2:$BS2),2)
=MOD(COLUMN(INDEX('Resource Calcs'!$BF2:$BS2,2)),2),
'Resource Calcs'!$BF2:$BS2),MATCH('Resource By Div'!$A$1,
IF(MOD(COLUMN('Resource Calcs'!$BF2:$BS2),2)
=MOD(COLUMN(INDEX('Resource Calcs'!$BF2:$BS2,1)),2),
'Resource Calcs'!$BF2:$BS2),0)+1))

Notes:
1) this is a relatively expensive formula and may slow your sistem down
significantly if copied to too many cells.
2) the good news is that it doesn't seem to be volatile, so it will only
recalculate if the affected ranges change
Click to expand...

You seem to be trying for as general as possible. First, for
simplicity, I'll replace 'Resource Calcs'!$BF2:$BS2 with rng.

=IF('Resource Calcs'!$B2="","",'Resource Calcs'!$B2
*INDEX(IF(MOD(COLUMN(rng),2)=MOD(COLUMN(INDEX(rng,2)),2),rng),
MATCH('Resource By Div'!$A$1,
IF(MOD(COLUMN(rng),2)=MOD(COLUMN(INDEX(rng,1)),2),rng),0)+1))

Much simplification possible.

=IF('Resource Calcs'!$B2="","",'Resource Calcs'!$B2
*INDEX(rng,MATCH(1,(rng='Resource By Div'!$A$1)
*(MOD(COLUMN(rng)-COLUMN(INDEX(rng,1)),2)=0),0)+1))
 
Harlan Grove said:
Much simplification possible.
=IF('Resource Calcs'!$B2="","",'Resource Calcs'!$B2
*INDEX(rng,MATCH(1,(rng='Resource By Div'!$A$1)
*(MOD(COLUMN(rng)-COLUMN(INDEX(rng,1)),2)=0),0)+1))
Click to expand...

Thanks Harlan - that's much better in deed.

Regards,
KL
 
Back
Top