Formula help needed

[Ola Sigurdh]

Can someone help me to shorten this formula, it slows down my computer so it
takes ages to recalculate the sheet when I enter something in it.
It is used to calculate time, basic formula (A2-A1+(A2<A1)) from C
Pearsson´s site

OFFSET(INDEX(Tid3;MATCH(A8&1;Feb!$A$9:$A$250&Feb!$B$9:$B$250;0);MATCH(Feb!$C
$8;Feb!$A$8:$D$8;0);1);1;0)-INDEX(Tid3;MATCH(A8&1;Feb!$A$9:$A$250&Feb!$B$9:$
B$250;0);MATCH(Feb!$C$8;Feb!$A$8:$D$8;0);1)+(OFFSET(MATCH(Tid3;PASSA(A8&1;Fe
b!$A$9:$A$250&Feb!$B$9:$B$250;0);MATCH(Feb!$C$8;Feb!$A$8:$D$8;0);1);1;0)<IND
EX(Tid3;MATCH(A8&1;Feb!$A$9:$A$250&Feb!$B$9:$B$250;0);MATCH(Feb!$C$8;Feb!$A$
8:$D$8;0);1))))

TIA

Ola Sigurdh

 

[Anne Troy]

I don't want to sound rude, but the first thing that I would do is redesign
my workbook. Remember, there are only several ways to calculate data, but
there are many, many ways to DISPLAY data from Excel, such as a "report
page" which contains links to the data worksheet, or using Mail merge with
Word. It seems to me that if you make your data into *flat* rows and
columns, your formulas will become efficient.
**** Hope it helps! ****

~Dreamboat
Excel VBA Certification Coming Soon!
www.VBAExpress.com/training/
********************************

 

[Ola Sigurdh]

Hello Anne

I have one page where I put all my data and one report page where I sum it.
In my datapage the layout is something like this. The formula is for pulling
time for each load every day and a similar formula is for pulling the miles
for each load. Then I have more columns with weigth price and so on.
A B C D
1 Date Load nr Time Miles
2 1 1 07:00 1000
3 1 1 15:00 1200
4
5 1 2 16:30 1200
6 1 2 20:00 1500
7 1 2
8 1 2
9
10 1 3 21:00 1500
11 1 3 03:30 1950
12 1 3
13 1 3
14 1 3
15 1 3
I have been thinking about the layout but I cant come up with a better one.

Tia

Ola Sigurdh

 

[Bob Phillips]

Try something like

=SUMPRODUCT(--(Sheet2!B1:B20=1)*(Sheet2!C1:C20))
for the total time for load 1, and

=SUMPRODUCT(--(Sheet2!B1:B20=1)*(Sheet2!D120))
for the miles for load 1

 

[Ola Sigurdh]

Hello Bob

I think you missunderstand my question. The times are start and end times, I
want to calculate the difference between them, the same for the miles. With
your formula I get the total not the elapsed time between the start and
endtimes.

TIA

Ola Sigurdh

 

[Bob Phillips]

If you put them on the same line, you can easily subtract. On separate
lines, it is more difficult.

 

[KellyMcG03]

I agree with Bob, if you could put a formula on each individual line an
then have a summary workbook that reference to those cells instead o
the orginals it would probably run much faster.

I found this formula in Excel Help maybe it could be useful to you?

=TEXT(B2-A2,"h:mm:ss") Hours, minutes and seconds between two time

 

[Ola Sigurdh]

Thank you for all your help. Your advice got me thinking in another
direction. I solved the problem with some helper columns in the first sheet.

Thank´s again

Ola Sigurdh