Math formula help

R

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

Auric__

Robert said:
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.)
 
J

joeu2004

Robert Crandal said:
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 said:
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().
 
J

joeu2004

PS: I said:
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))
 
R

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!
 
J

joeu2004

Robert Crandal said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top