Math formula help
 

Here is a sampling of data that I want to
represent with a math formula:

X Y
-- --
0.0 1
0.38 1
0.88 1
1.0 1
1.01 2
1.22 2
1.99 2
2.0 2
2.10 3
2.77 3
3.0 3

etc.. etc..

So, for example, if X equals 1.22, then Y
should return 2, etc, as seen above.

I thought I could easily represent the above
data with a simple math formula, but it is
more difficult than I thought.

Can anyone think of a simple math formula
for this? Do I need to use the RoundUp function?

I prefer a simple math formula that does not use
if-else logic or the less-than or greater-than sybols,
if possible. Let me know if you have any ideas.

Thanks.

Math formula help
 

Robert Crandal wrote:

Here is a sampling of data that I want to
represent with a math formula:

X Y
-- --
0.0 1
0.38 1
0.88 1
1.0 1
1.01 2
1.22 2
1.99 2
2.0 2
2.10 3
2.77 3
3.0 3

etc.. etc..

So, for example, if X equals 1.22, then Y
should return 2, etc, as seen above.

I thought I could easily represent the above
data with a simple math formula, but it is
more difficult than I thought.

Can anyone think of a simple math formula
for this? Do I need to use the RoundUp function?

I prefer a simple math formula that does not use
if-else logic or the less-than or greater-than sybols,
if possible. Let me know if you have any ideas.

With the exception of 0.0, it looks like you're just rounding up to the next
integer. If you're willing to accept a single IF to catch the zero, this
works:

=IF(A1=0,1,CEILING(A1,1))

(If you're not *just* rounding up, then this won't work.)

--
Ambushes aren't part of your typical forest.

Math formula help
 

"Robert Crandal" wrote:
Here is a sampling of data that I want to represent with a math formula:
X Y
-- --
0.0 1
0.38 1
0.88 1
1.0 1
1.01 2
1.22 2
1.99 2
2.0 2
2.10 3
2.77 3
3.0 3
etc.. etc..

What about negative X?



"Robert Crandal" wrote:
I prefer a simple math formula that does not use
if-else logic or the less-than or greater-than sybols,
if possible. Let me know if you have any ideas.

If you made a mistake and at X=0, Y should be 0, then perhaps simply:

=ROUNDUP(X1,0)

But that depends on what you want for X<0.

Otherwise, perhaps:

=MAX(1,ROUNDUP(X1,0))

But that assumes for X<0, Y should still be 1.

However, arguably, the use of MAX() is not "simple math that does not use"
IF().

Math formula help
 

PS: I wrote:
"Robert Crandal" wrote:
Here is a sampling of data that I want to represent with a math formula:
X Y
-- --
0.0 1
0.38 1
0.88 1
1.0 1
1.01 2
1.22 2
1.99 2
2.0 2
2.10 3
2.77 3
3.0 3
etc.. etc..

What about negative X?



"Robert Crandal" wrote:
I prefer a simple math formula that does not use
if-else logic or the less-than or greater-than sybols,
if possible. Let me know if you have any ideas.

If you made a mistake and at X=0, Y should be 0, then perhaps simply:

=ROUNDUP(X1,0)

But that depends on what you want for X<0.

Otherwise, perhaps:

=MAX(1,ROUNDUP(X1,0))

But that assumes for X<0, Y should still be 1.

However, arguably, the use of MAX() is not "simple math that does not use"
IF().

I just realized that you posted to m.p.e.programming. Are you interested in
a VBA expression?

If so, then the first case becomes:

WorksheetFunction.Roundup(X1,0)

and the second case becomes:

IIf(X1<=0,1,WorksheetFunction.Roundup(X1,0))

or

WorksheetFunction.Max(1,WorksheetFunction.Roundup( X1,0))

Math formula help (Thank you!)
 

Thank you Auric and Joeu2004 for your help.
Both of your responses helped very much.

Ultimately, the following code represents what I
was looking for:

'
' This is the VBA code I chose (assuming X is always =0) :
'
y = WorksheetFunction.Max(1, WorksheetFunction.RoundUp(x, 0))


Thanks again!

Math formula help (Thank you!)
 

"Robert Crandal" wrote:
Ultimately, the following code represents what I was looking for:
[....]
y = WorksheetFunction.Max(1, WorksheetFunction.RoundUp(x, 0))

Even though I offered up that solution, I'm curious: why do you want
something of that form instead of using IIf() or even If-Then-Else?

BTW, arguably, a purely "math" form might be:

y = -(x<=0) - (x0)*WorksheetFunction.Roundup(x,0)

Note that VBA converts True to -1, not 1 as Excel does.

But both that expression and the WorksheetFunction.Max expression are
probably less efficient than the IIf() expression I offered previously, to
wit:

y = IIf(x<=0,1,WorksheetFunction.Roundup(x,0))

And all are probably less efficient than:

If x0 Then y = WorksheetFunction.Roundup(x,0) Else y = 1

Math formula help (Thank you!)
 

"joeu2004" wrote

Even though I offered up that solution, I'm curious: why do you want
something of that form instead of using IIf() or even If-Then-Else?


I guess I thought a math-only formula would be more concise, elegant,
and efficient than if-else programming logic. Either approach would
have worked fine, but I guess I was just being picky. Thanks for
your solution, by the way.


BTW, arguably, a purely "math" form might be:

y = -(x<=0) - (x0)*WorksheetFunction.Roundup(x,0)


Hmm, but doesn't this code involve a little bit of if-else logic because
it's comparing X with 0, and returns True or False depending on
the value of X? I know there is no direct if-else VBA code here,
but doesn't this get converted to if-else instructions at the
assembly language level?


y = IIf(x<=0,1,WorksheetFunction.Roundup(x,0))

And all are probably less efficient than:

If x0 Then y = WorksheetFunction.Roundup(x,0) Else y = 1

So, the last example of code is the most efficient solution?
Even more efficient than the code that I chose to use:
y = WorksheetFunction.Max(1, WorksheetFunction.RoundUp(x, 0)) ??

Math formula help (Thank you!)
 

"Robert Crandal" wrote:
"joeu2004" wrote
y = -(x<=0) - (x0)*WorksheetFunction.Roundup(x,0)

Hmm, but doesn't this code involve a little bit of if-else
logic because it's comparing X with 0, and returns True or
False depending on the value of X?

Yes. So does the use of WorksheetFunction.Max internally.


"Robert Crandal" wrote:
doesn't this get converted to if-else instructions at the
assembly language level?

Yes. And it may or may not be more efficient than using
WorksheetFunction.Max. I was not suggesting it. I was just trying to get a
sense for what you meant by a "simple math formula that does not use if-else
logic".

In fact, if-then-else logic of some form is inescapable in your case because
of the need to handle x<=0 as a special case. It is simply a matter of how
explicit that logic appears in the program.


"Robert Crandal" wrote:
["joeu2004" wrote]
If x0 Then y = WorksheetFunction.Roundup(x,0) Else y = 1

So, the last example of code is the most efficient solution?
Even more efficient than the code that I chose to use:
y = WorksheetFunction.Max(1, WorksheetFunction.RoundUp(x, 0)) ??

Probably.

I don't know the internal design of VBA. But the explicit If-Then-Else
statement has the opportunity for some efficiencies, especially if it is
compiled or pseudo-compiled.

The WorksheetFunction.Max expression probably calls a general-purpose
assembly-language function that must be capable of processing a variable
number of parameters and selecting the largest one. And there is overhead
of evaluating and setting up the parameter list before the call.

If Max were a VBA function, it might be optimized and implemented exactly as
the explicit If-Then-Else. I doubt that is the case with
WorksheetFunction.Max. But if it were, at best, the two forms are simply
two ways of expressing exactly the same behavior in VBA.