Hour Run rate by month 1

[mattalin]

I would like to create a formula that will calculate a runrate, hours for example for a given number of periods. My current page will show the manager what the previous month's data was as well as what is scheduled for the current month per task. What I would like to add is a column called run rate, which would show the number of hours spent per period for the previous x months (I assume the formula would contain an average) for each task. Any ideas on how I should approach this with CR?

The problem is how can I get CR to determine the appropriate number of months. If I want a 3 month run rate but work was only performed in 2 months, I would end up averaging a 0 in my formula, which would understate my rate.

 

[MJRBIM]

I'm not clear...what exactly is it you are looking for?

Please post a sample of your table data with headers and a sample of your required results.

 

[Madawc]

In Crystal 8.5, running totals can calculate averages. They can also refrain for doing a calculation based on a formula, some value such as no work this month. (Though it might do this anyway, do a little trial and error.)

If I were doing this, I would also count the number of months left out because there was no work. Otehrwise the figures given could mislead.

Madawc Williams
East Anglia, Great Britain

 

[mattalin]

Here's an example of what I am trying to do:

Task Month1,Month2,Month3,Month4,%Comp,Run Rate

1	100,100,100,100,50%,100

2	0,0,100,100,50%,100

3	100,150,200,250,75%,175

4	100,100,0,0,100%,100

5	0,100,0,0,100%,100

Run Rate Formula:
Task 1=sum(Month1:Month4)/4 = 100
Task 2=sum(Month1:Month4)/2 = 100 Because no work was planned in 1st two months.
Task 3=sum(Month1:Month4)/4 = 175
Task 4=sum(Month1:Month4)/2 = 100 Task completed and experienced 100 hours/month
Task 5=sum(Month1:Month4)/1 = 100 Task completed and experienced 100 hours/month

 

[MJRBIM]

This could probably be combined into a single variable formula, but I broke it out into it's parts so that you can follow the logic.

All of these formulas go in your DETAIL section...

Name : {@Month1Value}
Formula : IF {Table.Month1} > 0 then 1 ELSE 0


Name : {@Month2Value}
Formula : IF {Table.Month2} > 0 then 1 ELSE 0


Name : {@Month3Value}
Formula : IF {Table.Month3} > 0 then 1 ELSE 0


Name : {@Month4Value}
Formula : IF {Table.Month4} > 0 then 1 ELSE 0


Name : {@MonthCount}
Formula : {@Month1Value} + {@Month2Value} + {@Month3Value} + {@Month4Value}


Name : {@RunRate}
Formula : ({Table.Month1} + {Table.Month2} + {Table.Month3} + {Table.Month4})/{@MonthCount}

Hope it helps....

 

[mattalin]

Thanks, this helps but it also gives me another idea on how to solve my problem more generically. I can have CR determine the period (months/weeks) actuals were incurred and simply divide by the total hours/dollars incurred.