Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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(). |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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)) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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)) ?? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
a simple math formula | New Users to Excel | |||
Math formula | Excel Discussion (Misc queries) | |||
Math Formula | Excel Discussion (Misc queries) | |||
Excel math formula | Excel Worksheet Functions | |||
simple math formula | Excel Discussion (Misc queries) |