Advanced cell function. is it possible

Hi all,


I`m having a problem on excel and I would like to know if it`s possible
to be done without programming in VB:

Let's say i have the following:

A B C
1 100 100 1100

The formula should be on C1, and it should evaluate to:

Everytime cells A1 and B1 have the same value, C1 = A1 + 1000

If B1 for example change to 200, C1 shoulb stay 1100, until A1 changes
to 200 also, when C1 should change to 1200.

i'll try to do some simulation to make it clear:

A B C
1 100 100 1100

A B C
1 200 100 1100

A B C
1 300 100 1100

A B C
1 300 300 1300

A B C
1 300 500 1500

Or, in other words, C1 should be equal to 1000 + the value of A1 or B1
on the last time they were equal.

Thanks in advance.

I would be delighted to be told I'm incorrect by a more knowledgable user but
I think you are going to struggle with this without resorting to VB. I can
see no way of retaining the old vlaues of A1 and B1 without doing so.

If you want a VB solution please post again and check that I have corrected
your logic correctly below.

Mike

"Tosco" wrote:

Hi all,


I`m having a problem on excel and I would like to know if it`s possible
to be done without programming in VB:

Let's say i have the following:

A B C
1 100 100 1100

The formula should be on C1, and it should evaluate to:

Everytime cells A1 and B1 have the same value, C1 = A1 + 1000

If B1 for example change to 200, C1 shoulb stay 1100, until A1 changes
to 200 also, when C1 should change to 1200.

i'll try to do some simulation to make it clear:

A B C
1 100 100 1100

A B C
1 200 100 1100

A B C
1 300 100 1100

A B C
1 300 300 1300 (1600)

A B C
1 300 500 1500 (1600)

Or, in other words, C1 should be equal to 1000 + the value of A1 or B1
on the last time they were equal.

Thanks in advance.

I think it's impossible to do without VBA.

Dave
--
Brevity is the soul of wit.


"Mike" wrote:

I would be delighted to be told I'm incorrect by a more knowledgable user but
I think you are going to struggle with this without resorting to VB. I can
see no way of retaining the old vlaues of A1 and B1 without doing so.

If you want a VB solution please post again and check that I have corrected
your logic correctly below.

Mike

"Tosco" wrote:

Hi all,


I`m having a problem on excel and I would like to know if it`s possible
to be done without programming in VB:

Let's say i have the following:

A B C
1 100 100 1100

The formula should be on C1, and it should evaluate to:

Everytime cells A1 and B1 have the same value, C1 = A1 + 1000

If B1 for example change to 200, C1 shoulb stay 1100, until A1 changes
to 200 also, when C1 should change to 1200.

i'll try to do some simulation to make it clear:

A B C
1 100 100 1100

A B C
1 200 100 1100

A B C
1 300 100 1100

A B C
1 300 300 1300 (1600)

A B C
1 300 500 1500 (1600)

Or, in other words, C1 should be equal to 1000 + the value of A1 or B1
on the last time they were equal.

Thanks in advance.

thanbks for your promptly repply.

Your answer was just what i wasen't expecting to have, but if thats the
only way, if you can post me a sample function in Vb that does it, it
would be great.

My main concern in not using Vb is the efficiency of it, as i'm going
to be using this formula in hundreds of cells, but if it's the only
way..

Can i use the value of the cell in my function and just change it if
the 2 others are different?

thanks again

On Jan 26, 5:50 pm, Dave F wrote:
I think it's impossible to do without VBA.

Dave
--
Brevity is the soul of wit.



"Mike" wrote:
I would be delighted to be told I'm incorrect by a more knowledgable user but
I think you are going to struggle with this without resorting to VB. I can
see no way of retaining the old vlaues of A1 and B1 without doing so.

If you want a VB solution please post again and check that I have corrected
your logic correctly below.

Mike

"Tosco" wrote:

Hi all,

I`m having a problem on excel and I would like to know if it`s possible
to be done without programming in VB:

Let's say i have the following:

A B C
1 100 100 1100

The formula should be on C1, and it should evaluate to:

Everytime cells A1 and B1 have the same value, C1 = A1 + 1000

If B1 for example change to 200, C1 shoulb stay 1100, until A1 changes
to 200 also, when C1 should change to 1200.

i'll try to do some simulation to make it clear:

A B C
1 100 100 1100

A B C
1 200 100 1100

A B C
1 300 100 1100

A B C
1 300 300 1300 (1600)

A B C
1 300 500 1500 (1600)

Or, in other words, C1 should be equal to 1000 + the value of A1 or B1
on the last time they were equal.

Thanks in advance.- Hide quoted text -- Show quoted text -

Actually, I'd recommend a VB solution, but by allowing a circular reference,
this can be done.

Under TOOLS--OPTIONS--CALCULATION Tab, check the box marked "Iterations"
and change "Maximum Interations" to 1.

Now, in C1, use this formula:

=IF(A1=B1,A1+1000,C1)

HTH,
Elkar


"Dave F" wrote:

I think it's impossible to do without VBA.

Dave
--
Brevity is the soul of wit.


"Mike" wrote:

I would be delighted to be told I'm incorrect by a more knowledgable user but
I think you are going to struggle with this without resorting to VB. I can
see no way of retaining the old vlaues of A1 and B1 without doing so.

If you want a VB solution please post again and check that I have corrected
your logic correctly below.

Mike

"Tosco" wrote:

Hi all,


I`m having a problem on excel and I would like to know if it`s possible
to be done without programming in VB:

Let's say i have the following:

A B C
1 100 100 1100

The formula should be on C1, and it should evaluate to:

Everytime cells A1 and B1 have the same value, C1 = A1 + 1000

If B1 for example change to 200, C1 shoulb stay 1100, until A1 changes
to 200 also, when C1 should change to 1200.

i'll try to do some simulation to make it clear:

A B C
1 100 100 1100

A B C
1 200 100 1100

A B C
1 300 100 1100

A B C
1 300 300 1300 (1600)

A B C
1 300 500 1500 (1600)

Or, in other words, C1 should be equal to 1000 + the value of A1 or B1
on the last time they were equal.

Thanks in advance.

Thanks again.

What should be the downside for your solution? just the goal seek will
not work?

Thsi worksheet uses RTD data feeds. Will it still work or i'll have
some problems?

Thanks


On Jan 26, 6:11 pm, Elkar wrote:
Actually, I'd recommend a VB solution, but by allowing a circular reference,
this can be done.

Under TOOLS--OPTIONS--CALCULATION Tab, check the box marked "Iterations"
and change "Maximum Interations" to 1.

Now, in C1, use this formula:

=IF(A1=B1,A1+1000,C1)

HTH,
Elkar



"Dave F" wrote:
I think it's impossible to do without VBA.

Dave
--
Brevity is the soul of wit.

"Mike" wrote:

I would be delighted to be told I'm incorrect by a more knowledgable user but
I think you are going to struggle with this without resorting to VB. I can
see no way of retaining the old vlaues of A1 and B1 without doing so.

If you want a VB solution please post again and check that I have corrected
your logic correctly below.

Mike

"Tosco" wrote:

Hi all,

I`m having a problem on excel and I would like to know if it`s possible
to be done without programming in VB:

Let's say i have the following:

A B C
1 100 100 1100

The formula should be on C1, and it should evaluate to:

Everytime cells A1 and B1 have the same value, C1 = A1 + 1000

If B1 for example change to 200, C1 shoulb stay 1100, until A1 changes
to 200 also, when C1 should change to 1200.

i'll try to do some simulation to make it clear:

A B C
1 100 100 1100

A B C
1 200 100 1100

A B C
1 300 100 1100

A B C
1 300 300 1300 (1600)

A B C
1 300 500 1500 (1600)

Or, in other words, C1 should be equal to 1000 + the value of A1 or B1
on the last time they were equal.

Thanks in advance.- Hide quoted text -- Show quoted text -

Tosco, Can you reconsider your example? I'm confused about the last line of
your example: A1 = 300, B1=500 and C1 = 1500 . Shouldn't that be C1=1300???

And then, how do you want this to work. I'm unclear what you mean with 'the
last time they were equal..." Are A1 and B1 over time steadily rising in
value and you want C1 to rise with it in a step-wise manner? So the sheet
should somehow remember what the previous values were?

"Tosco" wrote:

Hi all,


I`m having a problem on excel and I would like to know if it`s possible
to be done without programming in VB:

Let's say i have the following:

A B C
1 100 100 1100

The formula should be on C1, and it should evaluate to:

Everytime cells A1 and B1 have the same value, C1 = A1 + 1000

If B1 for example change to 200, C1 shoulb stay 1100, until A1 changes
to 200 also, when C1 should change to 1200.

i'll try to do some simulation to make it clear:

A B C
1 100 100 1100

A B C
1 200 100 1100

A B C
1 300 100 1100

A B C
1 300 300 1300

A B C
1 300 500 1500

Or, in other words, C1 should be equal to 1000 + the value of A1 or B1
on the last time they were equal.

Thanks in advance.

There are a couple problems that may arise from turning on Iterations. First
off, other formulas in your workbook are now allowed to use ciruclar
references as well. This is a minor problem, but may make catching errors
more difficult, especially in large/complex workbooks.

But, the main problem to look out for, is that the Iterations setting is
applied to all workbooks open in the same instance of Excel. So, if you set
Iterations on one workbook, it is applied to all other workbooks that may be
open. Same with turning off Iterations. And, most annoyingly, if you open
multiple workbooks, the Iterations setting of the first workbook opened will
be applied to each subsequently opened workbook, regardless of how that
workbook was saved.

I'm not saying you shouldn't use this, but just keep these things in mind.
Elkar

"Gustavo" wrote:

Thanks again.

What should be the downside for your solution? just the goal seek will
not work?

Thsi worksheet uses RTD data feeds. Will it still work or i'll have
some problems?

Thanks


On Jan 26, 6:11 pm, Elkar wrote:
Actually, I'd recommend a VB solution, but by allowing a circular reference,
this can be done.

Under TOOLS--OPTIONS--CALCULATION Tab, check the box marked "Iterations"
and change "Maximum Interations" to 1.

Now, in C1, use this formula:

=IF(A1=B1,A1+1000,C1)

HTH,
Elkar



"Dave F" wrote:
I think it's impossible to do without VBA.

Dave
--
Brevity is the soul of wit.

"Mike" wrote:

I would be delighted to be told I'm incorrect by a more knowledgable user but
I think you are going to struggle with this without resorting to VB. I can
see no way of retaining the old vlaues of A1 and B1 without doing so.

If you want a VB solution please post again and check that I have corrected
your logic correctly below.

Mike

"Tosco" wrote:

Hi all,

I`m having a problem on excel and I would like to know if it`s possible
to be done without programming in VB:

Let's say i have the following:

A B C
1 100 100 1100

The formula should be on C1, and it should evaluate to:

Everytime cells A1 and B1 have the same value, C1 = A1 + 1000

If B1 for example change to 200, C1 shoulb stay 1100, until A1 changes
to 200 also, when C1 should change to 1200.

i'll try to do some simulation to make it clear:

A B C
1 100 100 1100

A B C
1 200 100 1100

A B C
1 300 100 1100

A B C
1 300 300 1300 (1600)

A B C
1 300 500 1500 (1600)

Or, in other words, C1 should be equal to 1000 + the value of A1 or B1
on the last time they were equal.

Thanks in advance.- Hide quoted text -- Show quoted text -

Interesting solution to use iteration. Note also that you may see a
performance degradation with circular references, since the workbook will
recalculate entirely every time a change is made to it.

Dave
--
Brevity is the soul of wit.


"Elkar" wrote:

There are a couple problems that may arise from turning on Iterations. First
off, other formulas in your workbook are now allowed to use ciruclar
references as well. This is a minor problem, but may make catching errors
more difficult, especially in large/complex workbooks.

But, the main problem to look out for, is that the Iterations setting is
applied to all workbooks open in the same instance of Excel. So, if you set
Iterations on one workbook, it is applied to all other workbooks that may be
open. Same with turning off Iterations. And, most annoyingly, if you open
multiple workbooks, the Iterations setting of the first workbook opened will
be applied to each subsequently opened workbook, regardless of how that
workbook was saved.

I'm not saying you shouldn't use this, but just keep these things in mind.
Elkar

"Gustavo" wrote:

Thanks again.

What should be the downside for your solution? just the goal seek will
not work?

Thsi worksheet uses RTD data feeds. Will it still work or i'll have
some problems?

Thanks


On Jan 26, 6:11 pm, Elkar wrote:
Actually, I'd recommend a VB solution, but by allowing a circular reference,
this can be done.

Under TOOLS--OPTIONS--CALCULATION Tab, check the box marked "Iterations"
and change "Maximum Interations" to 1.

Now, in C1, use this formula:

=IF(A1=B1,A1+1000,C1)

HTH,
Elkar



"Dave F" wrote:
I think it's impossible to do without VBA.

Dave
--
Brevity is the soul of wit.

"Mike" wrote:

I would be delighted to be told I'm incorrect by a more knowledgable user but
I think you are going to struggle with this without resorting to VB. I can
see no way of retaining the old vlaues of A1 and B1 without doing so.

If you want a VB solution please post again and check that I have corrected
your logic correctly below.

Mike

"Tosco" wrote:

Hi all,

I`m having a problem on excel and I would like to know if it`s possible
to be done without programming in VB:

Let's say i have the following:

A B C
1 100 100 1100

The formula should be on C1, and it should evaluate to:

Everytime cells A1 and B1 have the same value, C1 = A1 + 1000

If B1 for example change to 200, C1 shoulb stay 1100, until A1 changes
to 200 also, when C1 should change to 1200.

i'll try to do some simulation to make it clear:

A B C
1 100 100 1100

A B C
1 200 100 1100

A B C
1 300 100 1100

A B C
1 300 300 1300 (1600)

A B C
1 300 500 1500 (1600)

Or, in other words, C1 should be equal to 1000 + the value of A1 or B1
on the last time they were equal.

Thanks in advance.- Hide quoted text -- Show quoted text -

Sorry somewhat late here's a VB solution

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Intersect(Target, Range("A1:b1")) Is Nothing Then
Exit Sub
Else
x = Worksheets("Sheet1").Cells(1, 1).Value
y = Worksheets("Sheet1").Cells(1, 2).Value
If x = y Then
Worksheets("Sheet1").Cells(1, 3).Value = x + y + 1000
End If
End If

End Sub

"Jop Duyvendak" wrote:

Tosco, Can you reconsider your example? I'm confused about the last line of
your example: A1 = 300, B1=500 and C1 = 1500 . Shouldn't that be C1=1300???

And then, how do you want this to work. I'm unclear what you mean with 'the
last time they were equal..." Are A1 and B1 over time steadily rising in
value and you want C1 to rise with it in a step-wise manner? So the sheet
should somehow remember what the previous values were?

"Tosco" wrote:

Hi all,


I`m having a problem on excel and I would like to know if it`s possible
to be done without programming in VB:

Let's say i have the following:

A B C
1 100 100 1100

The formula should be on C1, and it should evaluate to:

Everytime cells A1 and B1 have the same value, C1 = A1 + 1000

If B1 for example change to 200, C1 shoulb stay 1100, until A1 changes
to 200 also, when C1 should change to 1200.

i'll try to do some simulation to make it clear:

A B C
1 100 100 1100

A B C
1 200 100 1100

A B C
1 300 100 1100

A B C
1 300 300 1300

A B C
1 300 500 1500

Or, in other words, C1 should be equal to 1000 + the value of A1 or B1
on the last time they were equal.

Thanks in advance.