Improbable Formula - Which Function?

[holly]

I was wondering if there's a formula that would calculate a value based on
different variables. Example: If I had 4 diiferent values: 2.35; 5.09;
5.25; 3.25; 3.99 and I wanted a value returned that was based on this:

2.35; <5.09; <5.25; <=3.25; >3.99, what function would I use to enter these

variables?
=[FUNCTION?](>2.35; <5.09; <5.25; <=3.25; >3.99) but then I would need the
return value divided by 2.35 to be less than a value of 15.26%.

 

[Max]

One guess is that you're hinting at using Solver (under Tools > Solver)

---

I was wondering if there's a formula that would calculate a value based on
different variables. Example: If I had 4 diiferent values: 2.35; 5.09;
5.25; 3.25; 3.99 and I wanted a value returned that was based on this:

2.35; <5.09; <5.25; <=3.25; >3.99, what function would I use to enter these

variables?
=[FUNCTION?](>2.35; <5.09; <5.25; <=3.25; >3.99) but then I would need the
return value divided by 2.35 to be less than a value of 15.26%.

 

[ilia]

I don't know of any such function, but you could put together a UDF to
do this for you. I also don't understand your second requirement; no
value between 3.99 and 5.09 will return a value less than 15.26% when
divided by 2.35. However, here's a function that will take any number
of arguments as string, each beginning with either a < or a >, and
generate a random number within the specified parameters. So, you
would use it in a worksheet like so:

=ROUND(myFunction(">2.35","<5.09","<5.25","<=5.09",">3.99"), 2)

This is the code, to be placed in a standard module:

Public Function myFunction(ParamArray vals() As Variant) As Variant
On Error GoTo errorExit

Application.Volatile
Const incr As Long = 5

Dim smallest() As Double
Dim largest() As Double

Dim iSize As Long
Dim jSize As Long

Dim d As Double

Dim i As Long, j As Long
Dim k As Long

iSize = incr
ReDim smallest(1 To iSize)
jSize = incr
ReDim largest(1 To jSize)

For k = LBound(vals) To UBound(vals)
If Left$(vals(k), 1) = ">" Then
i = i + 1
If (i > iSize) Then
iSize = iSize + incr
ReDim Preserve smallest(1 To iSize)
End If
If Mid$(vals(k), 2, 1) <> "=" Then
d = CDbl(Right$(vals(k), Len(vals(k)) - 1))
Else
d = CDbl(Right$(vals(k), Len(vals(k)) - 2))
End If
smallest(i) = d
ElseIf (Left$(vals(k), 1) = "<") Then
j = j + 1
If (j > jSize) Then
jSize = jSize + incr
ReDim Preserve largest(1 To jSize)
End If
If Mid$(vals(k), 2, 1) <> "=" Then
d = CDbl(Right$(vals(k), Len(vals(k)) - 1))
Else
d = CDbl(Right$(vals(k), Len(vals(k)) - 2))
End If

largest(j) = d
Else
Err.Raise (3333)
End If
Next k

If (i < iSize) Then
ReDim Preserve smallest(1 To i)
End If

If (j < jSize) Then
ReDim Preserve largest(1 To j)
End If

With Application.WorksheetFunction
If (.Max(smallest) > .Min(largest)) Then
Err.Raise (3333)
Else
myFunction = Rnd() * (.Min(largest) - .Max(smallest))
+ .Max(smallest)
End If
End With
Exit Function

errorExit:
myFunction = CVErr(XlCVError.xlErrValue)
Exit Function
End Function

 

[Tyro]

Suggest you restate your question with examples.

Tyro

I was wondering if there's a formula that would calculate a value based on
different variables. Example: If I had 4 diiferent values: 2.35; 5.09;
5.25; 3.25; 3.99 and I wanted a value returned that was based on this:

2.35; <5.09; <5.25; <=3.25; >3.99, what function would I use to enter
these

variables?
=[FUNCTION?](>2.35; <5.09; <5.25; <=3.25; >3.99) but then I would need the
return value divided by 2.35 to be less than a value of 15.26%.

 

[holly]

Those numbers were just random numbers, I have about 1,000 lines of info and
around 10 values that need to be eveluated in each line. So I need a formula
that will work regardless of the outcome because the return value has to be
less than this number and greater than this, etc. You see there's too many
to do manually. I tried putting the code into a module and it didn't work.

Suggest you restate your question with examples.

Tyro

I was wondering if there's a formula that would calculate a value based on
different variables. Example: If I had 4 diiferent values: 2.35; 5.09;
5.25; 3.25; 3.99 and I wanted a value returned that was based on this:

2.35; <5.09; <5.25; <=3.25; >3.99, what function would I use to enter
these

variables?
=[FUNCTION?](>2.35; <5.09; <5.25; <=3.25; >3.99) but then I would need the
return value divided by 2.35 to be less than a value of 15.26%.

 

[Sandy Mann]

I think that you will still have to re-state you question again:

How can any value be <=3.25 but at the same time >3.99?

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

Those numbers were just random numbers, I have about 1,000 lines of info
and
around 10 values that need to be eveluated in each line. So I need a
formula
that will work regardless of the outcome because the return value has to
be
less than this number and greater than this, etc. You see there's too
many
to do manually. I tried putting the code into a module and it didn't
work.

Suggest you restate your question with examples.

Tyro

I was wondering if there's a formula that would calculate a value based
on
different variables. Example: If I had 4 diiferent values: 2.35;
5.09;
5.25; 3.25; 3.99 and I wanted a value returned that was based on this:
2.35; <5.09; <5.25; <=3.25; >3.99, what function would I use to enter
these
variables?
=[FUNCTION?](>2.35; <5.09; <5.25; <=3.25; >3.99) but then I would need
the
return value divided by 2.35 to be less than a value of 15.26%.

 

[ilia]

Mine worked just fine, so you must be doing something wrong. Don't
you think I tested it before posting? Try to do Debug -> Compile and
see if you get any errors (most commonly line breaks due to line
length limitations in posts). Even when compile doesn't catch errors,
the function will still return errors if any of your string don't
begin with < or >, and if there are conflicting conditions that make
the number impossible. You didn't specify the below-percent-of
parameter in your example so I didn't put that part in

Those numbers were just random numbers, I have about 1,000 lines of info and
around 10 values that need to be eveluated in each line. So I need a formula
that will work regardless of the outcome because the return value has to be
less than this number and greater than this, etc. You see there's too many
to do manually. I tried putting the code into a module and it didn't work.

Suggest you restate your question with examples.

Tyro

I was wondering if there's a formula that would calculate a value based on
different variables. Example: If I had 4 diiferent values: 2.35; 5.09;
5.25; 3.25; 3.99 and I wanted a value returned that was based on this:
2.35; <5.09; <5.25; <=3.25; >3.99, what function would I use to enter
these
variables?
=[FUNCTION?](>2.35; <5.09; <5.25; <=3.25; >3.99) but then I would need the
return value divided by 2.35 to be less than a value of 15.26%.