Hi Alan

Having slept, further thoughts on your problem.
I'm not absolutely sure that I understand your layout. If you have 4
revenue rows per asset, isn't your matrix 1200 rows by 65 columns?

Anyway, the way I think I would tackle it would be as follows.
On the sheet with Asset data, I would have a table with Asset name,
Acquisition Date and Disposal date in the form

AAAA 200408 200712
BBBB 200606 200901
This I would set up as a Named range, InsertNameDefine Name Asset
Refers to = OFFSET(Sheet2!$A$1,0,0,COUNTA(Sheet2!$A:$A),3)

On my Main sheet
A2: Asset Name B2:Utilisation C2: Daily Rate
D1 First Month of 60 as an Excel date 01/01/2007 formatted however you
wish e.g. Jan 07
Continue Calendar dates across row 1 for a further 59 columns.
In D2 Number of days per month, again continued across

I would then set up 3 named formulae, InsertNameDefine
Name xst1
Refers to =VLOOKUP(Sheet2!$A3,Asset,2,0)<=--(TEXT(Sheet2!D$1,"yyyymm"))
Name xst2
Refers to =VLOOKUP(Sheet2!$A3,Asset,3,0)=--(TEXT(Sheet2!D$1,"yyyymm"))
Name Calc
Refers to =xst1*xst2*Sheet2!$B3*Sheet2!$C3

Then in D3 the formula would be
=Calc*D$2
Copied across and down as required.
--
Regards

Roger Govier


"Alan" wrote in message
...

Hi Roger,

Individually, it is not much of an issue, but when multiplied up over
many sections it probably get magnified.

As is, the workbook is currently around 38Mb mainly in those
calculations.

Of course, there is an argument that we should migrate on from excel,
but for now that option is off the table and I need to optimise what I
have.

Thanks,
--
Alan.

The views expressed are my own, and not those of my employer or anyone
else associated with me.

My current valid email address is:



This is valid as is. It is not munged, or altered at all.

It will be valid for AT LEAST one month from the date of this post.

If you are trying to contact me after that time,
it MAY still be valid, but may also have been
deactivated due to spam. If so, and you want
to contact me by email, try searching for a
more recent post by me to find my current
email address.

The following is a (probably!) totally unique
and meaningless string of characters that you
can use to find posts by me in a search engine:

ewygchvboocno43vb674b6nq46tvb




"Roger Govier" wrote in message
...
Hi Alan

Since, when you multiply Boolean values together, the True's are
coerced to 1's and the False's to 0's, then
=A1*B1*C1*D1*E1
doesn't seem too much of a problem to me, even if you do have many
rows.

--
Regards

Roger Govier


"Alan" wrote in message
...

Hi All,

Bear with me on this!

I have a large workbook that we use for calculating financial
forecasts for a fleet hire business.

The workbook contains a total of 300 vehicles, and I need to do
calculations of revenue across five years (60 monthly buckets).

Currently there are four revenue lines for each vehicle for each
month and I am doing the following calculation for each:

1) Check that the vehicle has been acquired (picked up from an asset
sheet) - Boolean
2) Check that the vehicle has not been disposed (also from asset
sheet) - Boolean
3) Occupancy rate - Percentage
4) Days in month - Days
5) Daily average rate - $ / day

Multiply those five together to get a $ figure for the month across
the 300 vehicles.

The first four of those are the same for each revenue line (consider
them to be Hire, Extras, Insurance Waiver, and Other Bookings).

Obviously each calculation grid is big (300 x 60 = 18,000
calculations).

I could add another calculation area to do just the first four
items, and then refer to that and bring in the fifth item.

If I did that, it would obviously create an additional 18,000
calculation cells, but it would (theoretically at least) reduce the
actual number of calculations for excel by 3 x 18000 = 54,000 making
a net saving of 36,000 calcs.

However, it would also increase the dependency chain for each of the
final calculations by 1.


Question: Is it better to pull the common calculation out of the
other ones, and refer to it (feels like the right thing to do) or
should I prefer a smaller number of calculation cells?

Thanks,
--
Alan.

The views expressed are my own, and not those of my employer or
anyone
else associated with me.

My current valid email address is:



This is valid as is. It is not munged, or altered at all.

It will be valid for AT LEAST one month from the date of this post.

If you are trying to contact me after that time,
it MAY still be valid, but may also have been
deactivated due to spam. If so, and you want
to contact me by email, try searching for a
more recent post by me to find my current
email address.

The following is a (probably!) totally unique
and meaningless string of characters that you
can use to find posts by me in a search engine:

ewygchvboocno43vb674b6nq46tvb