Rounding up decimal values < .5 to next whole value 2

[npp83]

CR 2008 12.2

If I have an averaged value, calculated from a formula field that is less than the tradditional .5 barrier for rounding up, how can I round up the next whole number rather than rounding it down?

For example, I have 0.33 and I want it to be 1.0 and not 0.0

Somehow, need to incorporate the solution with the following snippet of code:

whileprintingrecords;
numbervar sumcnttwo;
numbervar cnttwo;
if cnttwo > 0 then sumcnttwo/cnttwo

ideas?

 

[briangriffin]

Floor and ceiling are useful functions for this type of requirement.

floor(1.9) = 1

ceiling(1.1) = 2

So you'll probably want:

whileprintingrecords;
numbervar sumcnttwo;
numbervar cnttwo;
if cnttwo > 0 then ceiling(sumcnttwo/cnttwo)

 

[npp83]

I could see how those would be useful but it seems those are hardcoded values. I would need them to be flexible for a variety of combinations. For example, hard-code my values like that, wouldnt I have trouble if I encountered a 10.09? .09 is less than one, but I still need it to bump up to 11.

Second, if I could get around the hardcoding of my ceiling values then where would I declare the floor/ceiling in my code? before the operation?

 

[briangriffin]

Those were just examples of how the functions operate.

You can pass any database field or calculated value in:

ceiling(ValueGoesHere)

So in your example if sumcnttwo/cnttwo = 10.09 then

ceiling(sumcnttwo/cnttwo) would evaluate to 11.

I think your variable formula will work with my addition.

 

[npp83]

Oddly enough, I implemented your solution and now where I should be getting values higher than 1, they all evaluate to 1. So even where I have numerators (when added up by hand) that are 8.xx and are being divided by say 3 I am getting just 1...

Do I have to define the ceiling value threshold by hand. Does it have to be declared somewhere?

 

[briangriffin]

Please post your formula and some sample data.

If sumcnttwo = 8 and cnttwo = 3 then

sumcnttwo/cnttwo = 2.6666

and

ceiling(sumcnttwo/cnttwo) = 2

 

[lbass]

Ceiling should work fine or you could just use roundup(), as I suggested earlier.

whileprintingrecords;
numbervar sumcnttwo;
numbervar cnttwo;
if cnttwo > 0 then
roundup(sumcnttwo/cnttwo,0)

-LB

 

[npp83]

Visit my website (

http://www.nppetre.com)

I took it down and threw on screenshot of what my report looks like with the ceiling applied. you will notice if you do the addition on the numerator and divde by the denominator the result is obviously wrong given by the number of the numerator.

Here is my code that gives me my sumcnt and my cnt

whileprintingrecords;
numbervar sumcnttwo := sumcnttwo +
{LOANXDB_N_02._CX_COUNT_COND_ASSET};
numbervar cnttwo := cnttwo + 1;

This gives me an average:
whileprintingrecords;
numbervar sumcnttwo;
numbervar cnttwo;
if cnttwo > 0 then ceiling(sumcnttwo/cnttwo)

whileprintingrecords;
numbervar sumcnttwo;
numbervar cnttwo;
if not inrepeatedgroupheader then (sumcnttwo := 0; cnttwo := 0);

 

[lbass]

We don't know what formulas are placed where or what you are trying to do here.

-LB

 

[npp83]

The second screenshot on my website now includes where what formulas are placed where.

What I am trying to do is calculate the averages by Branch (my first group (group #1) and if you take my two columns corresponding to sumcnt/sumcnttwo and cnt/cnttwo and perform an average on them, in many cases I will be getting an average that comes up as 0.33. This is okay. This is what I want, but this situation comes up typically where I have a loan that has an income or asset value of 1 and the other loans are 0

For example:
sumcnt | cnt
------------
1 1
0 2
0 3

If sumcnt = 1/cnt =3 then 1/3 = .33

Here is where it gets interesting. I have a loan with an Asset value of 1 and the other two are zeros. I still have 1 loan but it tells me the average is .33. So I need a way to always take a decimal value that is less than a whole number and round it up to the next whole number. E.G.
.33 becomes 1 and 4.67 becomes 5 and 2.45 becomes 3. Its like how you can't have 1.4 people. You know very well despite the math that it really represents 2 people.

So now, my problem with implementing ceiling(sumcnt/cnt) or RoundUp(sumcnt/cnt) is that the math on my averages is wrong. It seems to me that although my sumcnt total may be 8 and my cnt total is 3, its not giving me 2.67, but its resorting to 1.0 ... See my third picture on my site where the average shows one but despite implementing ceiling() I am getting 1 and not 2, which should round up.

That's basically it. Thanks to you both for your help.

 

[lbass]

It looks like you are adding the formulas twice--you can't do this--the variable will evaluate two times if you do. Remove one set of variables. Or maybe you meant us to know they were different? But htese aren't even the actual formulas, as they don't show the ceiling/roundup functions etiher.

And the average of 1,0, and 0 people is .33 people, not 1.

-LB

 

[npp83]

Right, I know its .33. It is my sole objective to use a function to make it always round up to the next whole integer, in this case it would be 1. You both suggested RoundUp and ceiling as a means of doing this. I applied it and have yet to see how it works. But as you pointed out I have done something wrong. I'd like to explore that.

I did display the originals without the ceiling function applied, but the third s/s does include the ceiling function. The results of that specific s/s are flawed.

Could you indicate which snippet of code or line you see me adding the formulas together by? I'd definitely appreciate that.

 

[npp83]

I now see (after much sleep) where you might think I have duplicated variables. No, these have been renamed according to column. sumcnt and sumcnttwo etc... I am not sure what that leaves me with

 

[briangriffin]

In looking at your screenshot, you do have the variables evaluating twice - in GH1 you have the {@loanoffice} formula twice (which for a reset formula has no effect on the report), and in the Details section you have the {@loangroup} formula inserted twice, which will have an impact on the variable values.

I would suggest that you remove the duplicate formulas, and get the report working without the ceiling function added in. In other words, have the report showing accurate figures with decimals - 1.33 people, etc.

After that is validated then you can worry about rounding those values up.

 

[npp83]

As I said in my previous post, they do appear that way in my s/s, and I even used a single piece of code to point to them both. That isn't the case, and its my fault for noit making that clear.

Each one is unique. @loangroup and @loangroup2 both have their own code with their own unique variables. For example, in @loangroup I have sumcnt and cnt and it @loangroup2 I have sumcnttwo and cnttwo. And from that, without the ceiling() function applied, I do have the right math going on. 1/3 = .33 etc..., but when I apply ceiling to the formula that does the average (as we discussed), it does not round .33 up to 1. And thats what I want: where I have a decimal value (even below .5) I want it to round up to the next whole number. For example, 2.33 would evaluate to 3.0

 

[briangriffin]

Can you get the report to display 1.33 in the group footer? If so, please post screen shot of that - I'm guessing that it's working, but you have the field formatted to display no decimals.

And put this formula in the group footer, just for a reality check:

ceiling(1.33)

 

[npp83]

You know what. I just went ahead, closed down the report without saving, restarted the report - and everything works. I may have mistyped something. I thank you for sticking with me on this confusing problem. I am the only one that really knows CR at work and I am still learning it as well. Thumbs up to both of you.