Math formula help

[Robert Crandal]

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.

 

[Auric__]

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.)

 

[joeu2004]

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?

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().

 

[joeu2004]

What about negative X?





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))

 

[Robert Crandal]

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!

 

[joeu2004]

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) - (x>0)*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 x>0 Then y = WorksheetFunction.Roundup(x,0) Else y = 1