How can one set a value TO another cell?

[blixel]

Suppose column A contains a list of dates.

A1 is July 21, 2008
A2 is July 22, 2008
A3 is July 23, 2008

And so on. The dates in column A are determined by a formula, so they
will change from day to day.

Suppose column B contains a list of daily prices.

B1 is $80
B2 is $90
B3 is $100

And so on. The prices in column B are determined by a formula, so
they will change from day to day.

Suppose column C contains a formula that calculates the data in column
B. The first time that formula in column C finds a cell in column B
that exceeds $100, I want it to update cell D1 to contain the date in
column A.

So the formula looks at B1, does a calculation, but is content.
The formula looks at B2, does a calculation, but is content.
The formula looks at B3 and "sees" that B3 exceeds $100 so the formula
tells D1 to inherit the date of A3.

Does that make sense?

I know I can go to cell D1 and type "=A3" but that's not the point.
The cell that contains the correct date may be A4 tomorrow, and A10
the following day. So hard coding D1 to A3 will not work for this
purpose.

I want the formula in column C to "push" the date to D1.

 

[Stefania]

Hi blixel,

If you're using colum C to calculate data, then write the following formula
in column D and copy it down:

=IF(B1>100,A1)

Hope this helps! ;o)

 

[blixel]

Hi blixel,

If you're using colum C to calculate data, then write the followingformula
in column D and copy it down:

=IF(B1>100,A1)

Thanks but that won't work in this case unless I want to have over
2,000 nested IF statements in cell D1.

IF(B1>100,A1,IF(B2>100,A2,IF(B3>100,A3))) (And so on * thousands)

What I need is for the formula in column C to "push" the date to D1.
(IF[calculations are true],[SET.D1(A1)])

 

[Tom Hutchins]

An Excel function can't put a value in another cell; it just returns a value
to the cell that contains the formula. I think the following array formula in
D1 will do what you want:

=INDIRECT(ADDRESS(MATCH(1,IF(B1:B2000>100,1,0),0),1))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do
it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Hope this helps,

Hutch

Hi blixel,

If you're using colum C to calculate data, then write the followingformula
in column D and copy it down:

=IF(B1>100,A1)

Thanks but that won't work in this case unless I want to have over
2,000 nested IF statements in cell D1.

IF(B1>100,A1,IF(B2>100,A2,IF(B3>100,A3))) (And so on * thousands)

What I need is for the formula in column C to "push" the date to D1.
(IF[calculations are true],[SET.D1(A1)])

 

[blixel]

An Excel function can't put a value inanothercell; it just returns a value
to thecellthat contains theformula. I think the following arrayformulain
D1 will do what you want:

=INDIRECT(ADDRESS(MATCH(1,IF(B1:B2000>100,1,0),0),1))

This is an arrayformula.  Hit ctrl-shift-enter instead of enter.  If you do
it
correctly, excel will wrap curly brackets {} around yourformula.  (don'ttype
them yourself.)

Hope this helps,

Thanks for the reply Tom. That did give some an additional insight
and I played around with a variety of ideas but still can't quite put
it together. I'll further explain what I'm trying to do and maybe you
or someone else can help bring it home for me.

I have a 1 file spreadsheet that has two sheets. The first sheet is
called "Main" and the second sheet is called "Data"

The Main sheet is intended to be an easily readable summary of the
Data sheet. The Data sheet contains 10 columns of data. In column A,
it's the date starting in cell A1 with a date that is equal to a cell
defined on the Main sheet. Then A2 and on done is simply A1+1, +A2+1,
A3+1, and so on. Very simple but it goes on for more than 2,000 rows.

The next important column for the sake of this problem is the %
column. The % column is calculated at 1.3% on a weekday (Monday
through Friday), and a lower percent on the weekend. I figured out
how to easily do this by doing if(mod(weekday(A1),
7)<=1,.005,.013)) ... so if it is a Monday-Friday, the day is
calculated at the higher percentage of 1.3%, if it's Saturday/Sunday,
the day is calculated at 0.05%

I keep track of this column on a daily basis which is just a simple
=sum($C$1:C2) for day two, and =sum($C$1:C3) for day two and so on all
the way down the line. Here is the "trick" ... when the sum of
column C becomes equal or greater than 125%, I need to know the exact
date that occurs on.

So on my Main sheet, in the proper summary cell, I need something like
=indirect(address(if(match($C$1:C2000>=125%)[return matching A column
date on that day])))

As a simple example, suppose the following. I'm using larger numbers
to keep the example short.

A1 = July 1, 2008 C1 = 25%
A2 = July 2, 2008 C2 = 25%
A3 = July 3, 2008 C3 = 25%
A4 = July 4, 2008 C4 = 15%
A6 = July 6, 2008 C4 = 15%
A7 = July 7, 2008 C4 = 25%
A8 = July 8, 2008 C4 = 25%

On July 7th, 2008 the total percentage will go to 130% which is over
the requirement. So I need the cell on the Main sheet to pull that
date from A7. Bear in mind that the date for A1 will change depending
on the situation. It's not hard coded. A1 could be any date.

 

[Tom Hutchins]

Sorry for the long delay responding. You can do this with a user-defined
function:

Public Function ThresholdDate(Rng As Range, Threshold As Double) As Date
Dim c As Range, tmpDate As Date
Dim RunTtl As Double
RunTtl = 0
For Each c In Rng
RunTtl = RunTtl + c.Value
If RunTtl > Threshold Then
tmpDate = c.Offset(0, -1).Value
Exit For
End If
Next c
ThresholdDate = tmpDate
End Function

The first argument, Rng, is the collection of percentages in column B on the
Data sheet. The second argument, Threshold, is the cumulative percentage
which, when exceeded, indicates the desired point in the data has been
reached. If the threshold is never attained, the formula returns 0. You would
call it from the Main sheet like this:

=ThresholdDate(Data!B1:B2000,125%) or
=ThresholdDate(Data!B1:B2000,1.25)

Since it returns a serial date value (from column A on the Data sheet), you
will need to format the cell with the formula to look like a date. You could
point the Threshold argument to a cell, so you could change the threshold
argument by changing that cell:

=ThresholdDate(Data!B1:B2000,H1)
where H1 contains 125%.

If you are new to user-defined functions (macros), this link to Jon
Peltier's site may be helpful:
http://peltiertech.com/WordPress/2008/03/09/how-to-use-someone-elses-macro/

Hope this helps,

Hutch

An Excel function can't put a value inanothercell; it just returns a value
to thecellthat contains theformula. I think the following arrayformulain
D1 will do what you want:

=INDIRECT(ADDRESS(MATCH(1,IF(B1:B2000>100,1,0),0),1))

This is an arrayformula. Hit ctrl-shift-enter instead of enter. If you do
it
correctly, excel will wrap curly brackets {} around yourformula. (don't type
them yourself.)

Hope this helps,

Thanks for the reply Tom. That did give some an additional insight
and I played around with a variety of ideas but still can't quite put
it together. I'll further explain what I'm trying to do and maybe you
or someone else can help bring it home for me.

I have a 1 file spreadsheet that has two sheets. The first sheet is
called "Main" and the second sheet is called "Data"

The Main sheet is intended to be an easily readable summary of the
Data sheet. The Data sheet contains 10 columns of data. In column A,
it's the date starting in cell A1 with a date that is equal to a cell
defined on the Main sheet. Then A2 and on done is simply A1+1, +A2+1,
A3+1, and so on. Very simple but it goes on for more than 2,000 rows.

The next important column for the sake of this problem is the %
column. The % column is calculated at 1.3% on a weekday (Monday
through Friday), and a lower percent on the weekend. I figured out
how to easily do this by doing if(mod(weekday(A1),
7)<=1,.005,.013)) ... so if it is a Monday-Friday, the day is
calculated at the higher percentage of 1.3%, if it's Saturday/Sunday,
the day is calculated at 0.05%

I keep track of this column on a daily basis which is just a simple
=sum($C$1:C2) for day two, and =sum($C$1:C3) for day two and so on all
the way down the line. Here is the "trick" ... when the sum of
column C becomes equal or greater than 125%, I need to know the exact
date that occurs on.

So on my Main sheet, in the proper summary cell, I need something like
=indirect(address(if(match($C$1:C2000>=125%)[return matching A column
date on that day])))

As a simple example, suppose the following. I'm using larger numbers
to keep the example short.

A1 = July 1, 2008 C1 = 25%
A2 = July 2, 2008 C2 = 25%
A3 = July 3, 2008 C3 = 25%
A4 = July 4, 2008 C4 = 15%
A6 = July 6, 2008 C4 = 15%
A7 = July 7, 2008 C4 = 25%
A8 = July 8, 2008 C4 = 25%

On July 7th, 2008 the total percentage will go to 130% which is over
the requirement. So I need the cell on the Main sheet to pull that
date from A7. Bear in mind that the date for A1 will change depending
on the situation. It's not hard coded. A1 could be any date.