Hi.

I have a range of cells from A1 thru D1 with a total of A1:D1 in E1 and a
total to compare that to in F1.

I want to take the difference between E1 and F1 and add it to the cell
between A1 and D1 that has the largest value.

Any ideas how I might accomplish this?

Thanks!

Liz,

Depends on which difference you want. Try

=E1-F1+MAX(A1:D1)

or

=F1-E1+MAX(A1:D1)

or even

=ABS(E1-F1)+MAX(A1:D1)

HTH,
Bernie
MS Excel MVP

"Liz C" wrote in message
...
Hi.

I have a range of cells from A1 thru D1 with a total of A1:D1 in E1 and a
total to compare that to in F1.

I want to take the difference between E1 and F1 and add it to the cell
between A1 and D1 that has the largest value.

Any ideas how I might accomplish this?

Thanks!

I want the difference between F1 & E1 to be added to the largest of A1 thru
J1. How can I have it figure out which cell is the largest and then go to
that cell and increase it's value by the difference between F1 & E1?
Thanks, Bernie.

"Bernie Deitrick" wrote:

Liz,

Depends on which difference you want. Try

=E1-F1+MAX(A1:D1)

or

=F1-E1+MAX(A1:D1)

or even

=ABS(E1-F1)+MAX(A1:D1)

HTH,
Bernie
MS Excel MVP

"Liz C" wrote in message
...
Hi.

I have a range of cells from A1 thru D1 with a total of A1:D1 in E1 and a
total to compare that to in F1.

I want to take the difference between E1 and F1 and add it to the cell
between A1 and D1 that has the largest value.

Any ideas how I might accomplish this?

Thanks!

Liz,

Now it's J1, not D1? or did you mis-type?

And do you actually want to change the value of the cell with the max value?
Then you would need a macro.

But the formula will simply give you the same result, but in another cell.

HTH,
Bernie
MS Excel MVP

"Liz C" wrote in message
...
I want the difference between F1 & E1 to be added to the largest of A1
thru
J1. How can I have it figure out which cell is the largest and then go to
that cell and increase it's value by the difference between F1 & E1?
Thanks, Bernie.

"Bernie Deitrick" wrote:

Liz,

Depends on which difference you want. Try

=E1-F1+MAX(A1:D1)

or

=F1-E1+MAX(A1:D1)

or even

=ABS(E1-F1)+MAX(A1:D1)

HTH,
Bernie
MS Excel MVP

"Liz C" wrote in message
...
Hi.

I have a range of cells from A1 thru D1 with a total of A1:D1 in E1
and a
total to compare that to in F1.

I want to take the difference between E1 and F1 and add it to the cell
between A1 and D1 that has the largest value.

Any ideas how I might accomplish this?

Thanks!

Sorry, I did mis-type. Yes, I guess what I need to know is how to make it go
to the cell that has the max value.

a1=50 b1=25 c1=10 d1=3 e1=(calculated)88 f1=90

I want it to go add 2 to a1 (because it is the max of a1 thru d1). I
figured I would need to do it with a macro in order to avoid a circular
reference, but don't know how to tell it to go to the cell with the max value.

Thanks.

Liz

"Bernie Deitrick" wrote:

Liz,

Now it's J1, not D1? or did you mis-type?

And do you actually want to change the value of the cell with the max value?
Then you would need a macro.

But the formula will simply give you the same result, but in another cell.

HTH,
Bernie
MS Excel MVP

"Liz C" wrote in message
...
I want the difference between F1 & E1 to be added to the largest of A1
thru
J1. How can I have it figure out which cell is the largest and then go to
that cell and increase it's value by the difference between F1 & E1?
Thanks, Bernie.

"Bernie Deitrick" wrote:

Liz,

Depends on which difference you want. Try

=E1-F1+MAX(A1:D1)

or

=F1-E1+MAX(A1:D1)

or even

=ABS(E1-F1)+MAX(A1:D1)

HTH,
Bernie
MS Excel MVP

"Liz C" wrote in message
...
Hi.

I have a range of cells from A1 thru D1 with a total of A1:D1 in E1
and a
total to compare that to in F1.

I want to take the difference between E1 and F1 and add it to the cell
between A1 and D1 that has the largest value.

Any ideas how I might accomplish this?

Thanks!

Liz C wrote...
I want the difference between F1 & E1 to be added to the largest of A1
thru
J1. How can I have it figure out which cell is the largest and then
go to
that cell and increase it's value by the difference between F1 & E1?
....

You originally said A1:D1, but now you say A1:J1. I'll assume you still
mean A1:D1. In short, you want A1:D1 to sum to the value in F1. Do all
cells in A1:D1 contain constant numeric values? If so, with a, b, c and
d representing the values originally in A1:D1, select A1:D1 and enter
the array formula

={a,b,c,d}+(COLUMN(INDIRECT("RC1:C"&COUNT({a,b,c,d }),0))=MATCH(MAX({a,b,c,d}),
{a,b,c,d},0))*(F1-SUM({a,b,c,d}))

Ohterwise, if you want the values automatically adjusted to sum to F1,
you'll need to use VBA to write a Calculate event handler.

Liz,

Copy the code below, right click on the worksheet tab, select "View code"
and paste the code into the window that appears.

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_Calculate()
Dim myCell As Range
Dim myVal As Double

If Range("E1").Value < Range("F1").Value Then
Application.EnableEvents = False
myVal = Application.WorksheetFunction.Max(Range("A1:D1"))
For Each myCell In Range("A1:D1")
If myCell.Value = myVal Then
myCell.Value = myCell.Value + Range("F1").Value - Range("E1").Value
Application.EnableEvents = True
Exit Sub
End If
Next myCell
End If
Application.EnableEvents = True
End Sub


"Liz C" wrote in message
...
Sorry, I did mis-type. Yes, I guess what I need to know is how to make it
go
to the cell that has the max value.

a1=50 b1=25 c1=10 d1=3 e1=(calculated)88 f1=90

I want it to go add 2 to a1 (because it is the max of a1 thru d1). I
figured I would need to do it with a macro in order to avoid a circular
reference, but don't know how to tell it to go to the cell with the max
value.

Thanks.

Liz

"Bernie Deitrick" wrote:

Liz,

Now it's J1, not D1? or did you mis-type?

And do you actually want to change the value of the cell with the max
value?
Then you would need a macro.

But the formula will simply give you the same result, but in another
cell.

HTH,
Bernie
MS Excel MVP

"Liz C" wrote in message
...
I want the difference between F1 & E1 to be added to the largest of A1
thru
J1. How can I have it figure out which cell is the largest and then
go to
that cell and increase it's value by the difference between F1 & E1?
Thanks, Bernie.

"Bernie Deitrick" wrote:

Liz,

Depends on which difference you want. Try

=E1-F1+MAX(A1:D1)

or

=F1-E1+MAX(A1:D1)

or even

=ABS(E1-F1)+MAX(A1:D1)

HTH,
Bernie
MS Excel MVP

"Liz C" wrote in message
...
Hi.

I have a range of cells from A1 thru D1 with a total of A1:D1 in
E1
and a
total to compare that to in F1.

I want to take the difference between E1 and F1 and add it to the
cell
between A1 and D1 that has the largest value.

Any ideas how I might accomplish this?

Thanks!

Thank you Bernie and Harlan!!
Have a great weekend!

"Harlan Grove" wrote:

Liz C wrote...
I want the difference between F1 & E1 to be added to the largest of A1
thru
J1. How can I have it figure out which cell is the largest and then
go to
that cell and increase it's value by the difference between F1 & E1?
....

You originally said A1:D1, but now you say A1:J1. I'll assume you still
mean A1:D1. In short, you want A1:D1 to sum to the value in F1. Do all
cells in A1:D1 contain constant numeric values? If so, with a, b, c and
d representing the values originally in A1:D1, select A1:D1 and enter
the array formula

={a,b,c,d}+(COLUMN(INDIRECT("RC1:C"&COUNT({a,b,c,d }),0))=MATCH(MAX({a,b,c,d}),
{a,b,c,d},0))*(F1-SUM({a,b,c,d}))

Ohterwise, if you want the values automatically adjusted to sum to F1,
you'll need to use VBA to write a Calculate event handler.