Data extraction 1

[KCarter]

Dear Friends

I need to construct a macro in excel that takes & extracts the highest figure from a series of data. From this figure it calculates the 10% value from the same data and displays both in a small table or form.

Forgive me if this seems ludicrously easy I have not yet been able to work much with Macros. Can you possibly help or provide information where I can seek it?

Kind regards

Kevin

 

[CautionMP]

A couple thoughts:[ol][li]You could use the DMax() function to return the maximum from a range.[/li][li]If you wanted to write your own function it would look something like this:

Public Function MaxPlus(ByRef Series As Range)
Dim CurrentValue As Variant
Dim ReturnValue As Variant
  For Each CurrentValue In Series.Value
    If CurrentValue > ReturnValue Then
      ReturnValue = CurrentValue
    End If
  Next
MaxPlus = ReturnValue
End Function

[/li][li]Either way, once you get the Maximum value you can then do a quick calculation to get the 10% value[/li][/ol]
Notes on usage:[ul][li]Open the VBE windoe [tt]<ALT> + F11[/tt][/li][li]Create a new blank module [tt]Insert=>Module[/tt][/li][li]Copy and Paste the code block above into the newly created Module[/li][li]Toggle back to the Excel window, higlight an empty cell[/li][li]Insert the newly created function into the cell [tt]Insert=>Function[/tt] and select MaxPlus from the list.[/li][/ul]

Hope this helps,
CMP


Instant programmer, just add coffee.

 

[SkipVought]

Hi,

If I'm reading this correctly, this could be done on a sheet with no coding at all.
[tt]
=MAX(YourDataRange)
=MAX(YourDataRange)*.1
[/tt]

Skip,

_{[red]Sign above the facsimile apparatus at the music publisher:[/red]
If it ain't baroque...
Don't FAX it!}