array calculation

Mike

Care to provide some example data, along with desired results?

Aladin

example 1:

a1 20/02/01
a2 21/02/01
a3 22/02/03
a4 23/02/01

b1 5
b2 0
b3 0
b4 20

output:

c1 5
c2 10
c3 15
c4 20

example 2:

a1 20/02/01
a2 21/02/01
a3 22/02/01
a4 23/02/01
(b0 25)
b1 0
b2 15
b3 0
b4 5

output:

c1 20
c2 15
c3 10
c4 5

can you help ?

example 1:

a1 20/02/01
a2 21/02/01
a3 22/02/03
a4 23/02/01

b1 5
b2 0
b3 0
b4 20

output:

c1 5
c2 10
c3 15
c4 20

example 2:

a1 20/02/01
a2 21/02/01
a3 22/02/01
a4 23/02/01
(b0 25)
b1 0
b2 15
b3 0
b4 5

output:

c1 20
c2 15
c3 10
c4 5

can you help ?

Mike

First the maths, then the mapping onto spreadheet formulas.

I don't seem to recognize the maths involved in obtaining the results in column C in your examples.

How (from what) do you get 10 in C2 and 15 in C3 in the first example. The same goes for the second example.

If we van have a verbal and/or mathematical description of how you've got those results, it shouldn't be too difficult to make Excel compute them.

Aladin

the 10 in c2 is calculated in that way that the formula in c2 should recognise, if b2 is zero and if it is, should search for the next not-zero-value above and below b2 and calculate the average, ie. it should show me (b3-b1)/(a3-a1)*(a2-a1)+b1 - (15-5)/2*1+5 = 10. but if there is a value different to zero in b2, it should show just b2. another complication: the cells, where there is a number or a zero can change their position (as shown in example 2).

thanks,
mike.

Mike

b1 5
b2 0
b3 0
b4 20

The above is your first example.
You say c2 would be equal to (15-5)/2*1+5. What I don't understand is where 15 comes from. It's not the value of b3, neither the value of b4 which is 20 (20 is the first non-zero value below b2). Can you clarify this bit?

Aladin

hi aladin,

i apologise. it should read (20-5)/3*1+5 as in (b4-b1)/(a4-a1)*(a2-a1)+b1. i mixed my example up a little bit. so you are right it should be the next value below, which is 20 in b4.

can you help ?

mike.

OK. The value in c3 (example 1) should be 7.5, not 15: because the denominator is (a4-a1)*(a3-a1) --> (3)*(2). If this is not correct, please how you did get that 15.

Aladin

no, it should be 15. for c3 the formula would be (b4-b1)/(a4-a1)*(a3-a1)+b1, in numbers 15/3*2+5 = 15.
comparing to c2 the only changed field is a2 to a3. so for c2 it would be 15/3*1+5 = 10.

This is easy, if the value's position stays the same (e.g. values always b1 and b4), but they are changing. For the next calculation they could be in cells b2 and b3. That is the core of the problem, I've got, not the calculation itself.

thanks for your help, mike.

HERE WE GO.
First the caveats: I cooked up a system of formulas that handle your first example, that is, the system produces numbers identical to your hand-calculated numbers. Assuming that your dates in A from A1 on, your numeric values in B from B1 on. I'm not sure whether the system is well-behaved if B1 contains a 0 instead of a value > 0.
I still don't recognize anything familiar to what you want to do.

C1 =IF(B1>0,0,INDIRECT(ADDRESS(LARGE((B2:$B$4<>0)*ROW(B2:$B$4),IF(COUNT(B2:$B$4)>2,2,1)),COLUMN(B2:$B$4)))) [must be array-entered and copied down to C2:C4 ]

D1 =IF(B1>0,0,INDIRECT(ADDRESS(MAX(($B$1:B1<>0)*ROW($B$1:B1)),COLUMN($B$1:B1)))) [must be array-entered and copied down to D2:D4 ]

E1 =IF(B1>0,"",INDIRECT(ADDRESS(LARGE((B2:$B$4<>0)*ROW(B2:$B$4),IF(COUNT(B2:$B$4)>2,2,1)),COLUMN(A2:$A$4)))) [must be array-entered and copied down to E2:E4 ]

F1 =IF(B1,"",A1) [ must be copied down to F2:F4 ]

G1 =IF(B1>0,"",INDIRECT(ADDRESS(MAX(($B$1:B1<>0)*ROW($B$1:B1)),COLUMN($A$1:A1)))) [must be array-entered and copied down to G2:G4 ]

H1 =IF(B1,B1) [ assume here a non-zero value -- needs reconsidering ]
H2 =IF(B2,B2,(((C2-D2)/(E2-G2))*(F2-G2))+D2) [ must be copied down to H3:H4 ]

H1:H4 will get populated with 5, 10, 15, 20.

Note. Array formulas must be entered by hitting control+shift+enter at the same time instead of just hitting enter.

I'm sure we can close on further if the system runs into problems.

You might want to have the workbook with the above machinery, instead of setting it up yourself. If so, let me know.

Cheers.

Aladin

Very impressive ! Thank you. That solves my problem ( b1 is always 1 - in my case anyway ).

Unfortunately, it opens another problem: as i have got over 8000 rows. excel keeps calculating and never finishes, because during calculation the data is changing again, so that it has to start calculating again. Is there a way to shortcut all this into one "lookup" function, so that excel only calcualtes the bit i am looking for (maybe via macro??) ??
Or is your existing workbook doing exactly that ??

Thanks very much,
mike.

PLEASED TO HEAR THAT.

HOW DO YOU MANAGE UPDATING? READ IN AUTOMATICALLY? WHAT IS UPDATE RATE?

Is there a way to shortcut all this into one "lookup" function,

BELIEVE ME I THOUGHT ABOUT IT. I STILL DO. I'LL TRY TO SIMPLIFY THE FORMULAS. KEEP YOU POSTED ON THE PROGRESS.

so that excel only calcualtes the bit i am looking for (maybe via macro??) ??

NO, JUST THE SYSTEM OF FORMULAS THAT I POSTED.

MIKE, ONE THING: WHAT IS IT FOR?

Aladin

yes. coming from a seperate database. possibly changing every couple of minutes.

that would be brilliant !

too modest ! the formulas showed me one more time that i am only using / knowing a fraction of what one can do with excel. is excel your profession or just a hobby, if i may ask ?

i am working on an interest rate calculator, which is supposed to get behind bank's business e.g. their mortgage rates.

thanks for your help.

Profession or hobby (Re: array calculation)

Mike,

Thanks for info. Concerning Excel: One of the subjects that I teach is data models or modelling. This includes also spreadsheet models.

Aladin