Active TopicsMemberlistSearchHelpRegisterLogin

Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: Avg formula using summary Post Reply Post New Topic
Author Message
talanis
Newbie
Newbie
Avatar

Joined: 20 Sep 2011
Location: United States
Online Status: Offline
Posts: 5
Quote talanis Replybullet Topic: Avg formula using summary
    Posted: 20 Sep 2011 at 6:36am

my first column is quantity, the next is extended pricing, in the third column I would like a formula.  ie (extended price/qty)  I have my columns of numbers with a Sum in the Group Footer.  Is there any way I can insert my formula as a 3rd column in the same Group Footer?  I keep getting avgs of the line above my formula.  Any help would be appreciated.  Thanks.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 20 Sep 2011 at 6:48am
you can create a formula field per row to show the average for the row
table.extended price / table.qty
then insert a summary of this formula field as an average for the group level 
 
IP IP Logged
talanis
Newbie
Newbie
Avatar

Joined: 20 Sep 2011
Location: United States
Online Status: Offline
Posts: 5
Quote talanis Replybullet Posted: 20 Sep 2011 at 6:53am
I get a division by zero error
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 20 Sep 2011 at 7:01am
if isnll(table.quantity) or table.quantity=0 then 0 else (table.extended price / table.qty)

Edited by DBlank - 20 Sep 2011 at 7:01am
IP IP Logged
talanis
Newbie
Newbie
Avatar

Joined: 20 Sep 2011
Location: United States
Online Status: Offline
Posts: 5
Quote talanis Replybullet Posted: 20 Sep 2011 at 7:08am
0.4
 23.39 58.48
0.4 23.39 58.48
0.4 23.39 58.48
6 3.3 0.55
6 3.3 0.55
6 3.3 0.55
1 31.25 31.25
1 31.25 31.25
1 31.25 31.25
22.2 173.82 270.84
7.82973 30.09333
Here are my numbers...
the 7.82973 is 173.82/22.2, the 30.09 is an average from 270.84
I am trying to get the  7.82973 number.  I know I am getting close but my terminology is holding me back.  Sorry.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 20 Sep 2011 at 7:21am
if sum(quantity,groupfield)=0 then 0 else
sum(pricing,groupfield) / sum(quantity,groupfield)
IP IP Logged
talanis
Newbie
Newbie
Avatar

Joined: 20 Sep 2011
Location: United States
Online Status: Offline
Posts: 5
Quote talanis Replybullet Posted: 20 Sep 2011 at 7:58am
Does groupfiled need to be changed to something else?
I am getting an error "The ) is missing."  Any ideas...
IP IP Logged
talanis
Newbie
Newbie
Avatar

Joined: 20 Sep 2011
Location: United States
Online Status: Offline
Posts: 5
Quote talanis Replybullet Posted: 20 Sep 2011 at 8:15am
FBlank - thank you for all your suggestions.  I finally got it!!! YEAH!
This is what finally worked...Sum ({Sheet1_.Extended}, {Sheet1_.Company})/Sum ({Sheet1_.Qty}, {Sheet1_.Company}).
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.016 seconds.
HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | CLICK PLAYS | COACH FORUMS | TROPICAL FISH

© 2004-2024 Brian Bischof. All Rights Reserved.