Calculation based on changing conditions

[xsrossiter]

Hello,

I am using Excel 2002 SP3 and hope to find a conditional solution
using formulas rather than a macro. In column A are a series of
values, column B has values at irregular intervals, and column C
contains calculated values based on a value in column B until a new
value in column B is encountered and then it incorporates the new
column B value in its calculation.

A B C
2 3 5
6 9
8 11
4 2 6
15 17
8 10
21 8 29

For example, column C contains a formula to sum columns A and B. It
adds 3 to whatever value is in column A until the fourth row at which
point it adds 2 to whatever value is in the A column and finally as it
gets to the seventh row begins adding 8 to A column values.

Is there some formula that can be placed in column C that will follow
this logic and avoid using a macro? Column A at this point has 86 rows
and column B has 14 values at varying intervals.

Thanks for your help and time.

Steve

 

[Ron Rosenfeld]

Hello,

I am using Excel 2002 SP3 and hope to find a conditional solution
using formulas rather than a macro. In column A are a series of
values, column B has values at irregular intervals, and column C
contains calculated values based on a value in column B until a new
value in column B is encountered and then it incorporates the new
column B value in its calculation.

A B C
2 3 5
6 9
8 11
4 2 6
15 17
8 10
21 8 29

For example, column C contains a formula to sum columns A and B. It
adds 3 to whatever value is in column A until the fourth row at which
point it adds 2 to whatever value is in the A column and finally as it
gets to the seventh row begins adding 8 to A column values.

Is there some formula that can be placed in column C that will follow
this logic and avoid using a macro? Column A at this point has 86 rows
and column B has 14 values at varying intervals.

Thanks for your help and time.

Steve

If your data starts in A1:

C1: =A1+LOOKUP(1E+307,$B$1:B1)

and fill down as far as required.

 

[transkawa]

If your data starts in A1:

C1: =A1+LOOKUP(1E+307,$B$1:B1)

and fill down as far as required.

I carried out your lookup() function, both for the array and the vector
form and it didn't work out as specified. I think the help files say the
lookup() function requires the lookup_vector be in ascending order for a
reliable result also although it is not a necessity.
My C1 returned FALSE instead of a numerical value.

 

[joeu2004]

C1: =A1+LOOKUP(1E+307,$B$1:B1)

it didn't work out as specified. [....] My C1 returned FALSE instead
of a numerical value.

Works fine for me, using the example that xsrossiter provided.
Moreover, I do not see how Ron's formula could return FALSE, at least
in Excel 2003, the version I have.

Normally, LOOKUP requires that the lookup vector is in ascending order
because LOOKUP uses a binary search. If you know how a binary search
works, it should be no surprise that LOOKUP(2,{2,1,3,4},{1,2,3,4})
returns 2 undesirably, and LOOKUP(1,{2,1,5,3,4},{1,2,3,4,5}) returns
#N/A.

But Ron's LOOKUP(1E307,...) relies on an accident of implementation,
namely: the binary search should always gravitate to the largest
number in the lookup vector, even if the lookup vector is not in
ascending order.

If you have a counter-example, I hope that you will post all the
details.

 

[joeu2004]

But Ron's LOOKUP(1E307,...) relies on an accident of implementation,
namely:  the binary search should always gravitate to the largest
number in the lookup vector, even if the lookup vector is not in
ascending order.

..... Because 1E307 is presumably larger than any number in column B.

Of course, there are larger possible numbers. The largest possible
number that can be entered manually is 9.99999999999999E+307. The
largest possible result of a computation is about 1.79769313486232E
+308, exactly (2^1023 - 2^(1023-53))*2.