Hi,

I have a row of values as follows with maximum upto 10 values in eac
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 remainin
values

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

I appreciate all your help.

Thank you.

Yusu

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

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/

After sending the post, I caught a math error - Here is the corrected version of the code:


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 = Int(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. _
RoundDown(WorksheetFunction.Log(c), 0) + 1
Y = Y + c / 10 ^ D
End If
Next i
Yaghanate = Y

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

End Function

If you don't have duplicate values in the range, you could use something like:
=--(MAX(A1:E1)&"."&SUBSTITUTE(A1&B1&C1&D1&E1,MAX(A1:E 1),""))
(you'll have to add your other columns into both sections of this formula).

Remember that excel has a limit of 15 significant digits. (type
123456789012345678 in a cell and you'll see what excel does (format it as
number, 0 decimal places).)

If you can return text:
=MAX(A1:E1)&"."&SUBSTITUTE(A1&B1&C1&D1&E1,MAX(A1:E 1),"")

With: 12 34 21 17 90 in A1:E1, I got:
90.12342117

as my result.




"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/

--

Dave Peterson