Yaghani,

If I understand what you want, the result of the first row would be 90.1234211790766 and the second would be 51.33212134 - right?

If so, here is some code that will perform that function. It has some error handling, but is not fully bullet proof. The tricky part is to span multiple ranges. If there will always be two ranges, the coding would be a trivial expansion of the code below. If it is an optional number of ranges, it gets more complicated. For what it's worth, here is the base code for handling a single range.

Brgds,
Mark

Public Function Yaghanate(Target As Range) As Double

On Error GoTo handleCancel
Application.EnableCancelKey = xlErrorHandler

M = WorksheetFunction.Max(Target)
AfterMax = False
Y = M
D = 0

For i = 1 To Target.Columns.Count
c = Target.Cells(1, i).Value + 0

If c = M And Not AfterMax Then
AfterMax = True
ElseIf c 0 And Not (WorksheetFunction.IsText(c) Or WorksheetFunction. _
IsError(Target.Cells(1, i).Value)) Then
D = D + WorksheetFunction.Max(1, WorksheetFunction.RoundUp( _
WorksheetFunction.Log(c), 0))
Y = Y + c / 10 ^ D
End If
Next i
Yaghanate = Y

handleCancel:
If Err 0 Then
CellRef = CVErr(xlErrValue)
End If

End Function


"yaghani " wrote:

Hi,

I have a row of values as follows with maximum upto 10 values in each
row:

<--- 5 values --- some other colummns <--- 5 values ---
12 34 21 17 90 76 6
51 33 21 21 34

I would like to compute a number out of these values as follows:
1. integer part of the number is maximum of all the values in a row
2. decimal part of the number is concatenation of all the remaining
values

I am relatively new to excel. I am not sure how to get it to work. I
want the final number to be calculated automatically whenever the
numbers in any of the cells change.

I appreciate all your help.

Thank you.

Yusuf


---
Message posted from http://www.ExcelForum.com/