Display the address of cell with max value

[Liz C]

Hi.

I have a range of cells from A1 thru D1 with a total of A11 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!

 

[Bernie Deitrick]

Liz,

Depends on which difference you want. Try

=E1-F1+MAX(A11)

or

=F1-E1+MAX(A11)

or even

=ABS(E1-F1)+MAX(A11)

HTH,
Bernie
MS Excel MVP

 

[Liz C]

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]

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]

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

 

[Harlan Grove]

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 A11, but now you say A1:J1. I'll assume you still
mean A11. In short, you want A11 to sum to the value in F1. Do all
cells in A11 contain constant numeric values? If so, with a, b, c and
d representing the values originally in A11, select A11 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.

 

[Bernie Deitrick]

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("A11"))
For Each myCell In Range("A11")
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]

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