interpolation with forecast in a particular interval (HELP)

[uriel78]

In column A I've got a series of increasing values

In column B I've got 100 values organized in such a way

B1:Bmax (increasing)
Bmax:B100 (floating)

Below there is an example of the situation: in this particular case
Bmax=B67=2997294 so I can think 2 intervals B1:B67 and B67:B100

Now, I have to to an interpolation to find the value in column A related to
0,8*Bmax, and I need to do this interpolation AFTER Bmax (in this case,
after B67, in other meanings in B67:B100)

Manually, to obtain the result I search, I do the following search between 2
contiguous values in the series:

In C2=0,8*Bmax=2397835 that is a value reached first time between B37:B38
and in a second time between B89:B90 (AFTER Bmax)
In D2=Forecast(C2;B89:B90;A89:A90)

Is there away to obtain this kind of interpolation automatically? I don't
know how I can define the interval of interpolation as "Bmax:B100"...as if I
put B1:B100 the result is the interpolation in the interval BEFORE Bmax (in
this case B37:B38)

:-??

I don't know how to do this...Hope S1 help me...

A B

1
0
0
2 0,000224 84884,93
3 0,000448 167409,5
4 0,000672 252295,4
5 0,000895 335277,1
6 0,00112 416715
7 0,001344 496897,3
8 0,001567 578183,4
9 0,001792 657461,2
10 0,002015 736563,9
11 0,002239 815298,4
12 0,002464 893326,9
13 0,002687 968955,6
14 0,002911 1038490
15 0,003135 1107973
16 0,003359 1177239
17 0,003583 1246595
18 0,003807 1314630
19 0,004031 1381607
20 0,004255 1448150
21 0,004479 1513580
22 0,004703 1577852
23 0,004927 1640861
24 0,005151 1702692
25 0,005374 1763200
26 0,005599 1822565
27 0,005823 1880872
28 0,006046 1938182
29 0,006271 1994534
30 0,006494 2049882
31 0,006718 2104075
32 0,006943 2157024
33 0,007166 2206536
34 0,00739 2251653
35 0,007614 2293608
36 0,007838 2334233
37 0,008062 2371962
38 0,008286 2408712
39 0,00851 2444735
40 0,008734 2479918
41 0,008958 2512678
42 0,009182 2544258
43 0,009406 2575218
44 0,00963 2604576
45 0,009853 2633294
46 0,010078 2661211
47 0,010302 2687478
48 0,010525 2711275
49 0,01075 2733830
50 0,010973 2754603
51 0,011197 2772971
52 0,011422 2789709
53 0,011645 2806214
54 0,011869 2822662
55 0,012093 2838530
56 0,012317 2853420
57 0,012542 2868303
58 0,012765 2883072
59 0,012989 2897991
60 0,013213 2911734
61 0,013437 2924944
62 0,013661 2937792
63 0,013885 2950164
64 0,014109 2961962
65 0,014333 2974800
66 0,014557 2986490
67 0,014781 2997294
68 0,015004 2891671
69 0,015229 2917447
70 0,015452 2822699
71 0,015676 2834802
72 0,015901 2803752
73 0,016124 2834110
74 0,016349 2850414
75 0,016572 2861214
76 0,016796 2741325
77 0,017021 2661400
78 0,017244 2682730
79 0,017468 2696406
80 0,017692 2709134
81 0,017916 2717095
82 0,01814 2619655
83 0,018364 2635156
84 0,018588 2647017
85 0,018812 2655551
86 0,019036 2646949
87 0,01926 2556849
88 0,019483 2494999
89 0,019708 2503824
90 0,019932 2397167
91 0,020155 2406423
92 0,02038 2412854
93 0,020603 2419818
94 0,020828 2425505
95 0,021051 2200234
96 0,021275 2210716
97 0,0215 2219757
98 0,021723 2226303
99 0,021947 2232247
100 0,022171 2237727
101 0,022395 2243487

 

[Bernd Plumhoff]

D2 should read:
=FORECAST(C2,INDEX(A1:A100,MATCH(MAX
(B1:B100),B1:B100,FALSE)):A100,INDEX(B1:B100,MATCH(MAX
(B1:B100),B1:B100,FALSE)):B100)

HTH,
Bernd

 

[uriel78]

Thank you for your answer!!
That's a very good solution...!!!

Now...I need to improve it to get more accuracy. With your tips I do the
forecast in Bmax:B100 correctly

How can I define the two contiguous cells Bi:Bi+1 (with Bi+1>0,8*Bmax>Bi)
that are in the sub-interval Bmax:B100 ?

Numerical example as referred in the first post Bi=B89 and Bi+1=B90

In C2=0,8*Bmax=2397835 that is a value reached first time between B37:B38
and in a second time between B89:B90 (AFTER Bmax)
In D2=Forecast(C2;B89:B90;A89:A90)

The FINAL problem, in fact, is:

searching for the value in "A column series" corresponding the first time
values in "B series" go lower than 0,8*Bmax (after reaching Bmax)

I hope in your final suggestions... I've tons of values to process in
such a way...:-/

 

[Bernd Plumhoff]

Enter in cell C3:
=INDEX(INDEX(A1:A101,MATCH(MAX
(B1:B101),B1:B101,FALSE)):A101,MATCH(TRUE,C2>INDEX
(B1:B101,MATCH(MAX(B1:B101),B1:B101,FALSE)):B101,FALSE))

Please enter above formula as array formula (with
CTRL+SHIFT+ENTER)

Then enter into cell C4:
="A"&MATCH(C3,A:A,FALSE)

C3 would show first value in column A where the
corresponding value in column B goes below 0.8 * MaxB
after MaxB.

C4 would show you the cell address of that cell (only if
values in column A are without duplicates!).

HTH,
Bernd

 

[uriel78]

That's great!!!Thank you very much!!!!!