Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
dagger
 
Posts: n/a
Default Limit or Exclude cells in Average and Sum formula


I am curious if anyone can help me out. I am trying to create both an
average and a sum formula that will limit or exclude certain fields.
Here is an example of what I'm working with:

Day1: 25
Day2: 20
Day3: 20
Day4: 25
Day5: 25
Weekly Total: 115

Day1: 15
Day2: 20
Day3: 30
Day4: 35
Day5: 25
Weekly Total: 125

Daily average= ?
Daily Total= ?

Is there any way to run an average excluding the weekly total fields
either by color or any other way. There is a SUM formula in the weekly
total field, can formula fields be excluded? The same for the average
of the daily numbers?

Thanks for any help you can provide.
Todd


--
dagger
------------------------------------------------------------------------
dagger's Profile: http://www.excelforum.com/member.php...o&userid=24996
View this thread: http://www.excelforum.com/showthread...hreadid=385228

  #2   Report Post  
Bob Phillips
 
Posts: n/a
Default

=SUM(IF(A1:A100<"Weekly Total"),B1:B100)

and

=AVERAGE(IF(A1:A100<"Weekly Total"),B1:B100)

Both are array formulae, so commit with Ctrl-Sift-Enter.

--
HTH

Bob Phillips

"dagger" wrote in
message ...

I am curious if anyone can help me out. I am trying to create both an
average and a sum formula that will limit or exclude certain fields.
Here is an example of what I'm working with:

Day1: 25
Day2: 20
Day3: 20
Day4: 25
Day5: 25
Weekly Total: 115

Day1: 15
Day2: 20
Day3: 30
Day4: 35
Day5: 25
Weekly Total: 125

Daily average= ?
Daily Total= ?

Is there any way to run an average excluding the weekly total fields
either by color or any other way. There is a SUM formula in the weekly
total field, can formula fields be excluded? The same for the average
of the daily numbers?

Thanks for any help you can provide.
Todd


--
dagger
------------------------------------------------------------------------
dagger's Profile:

http://www.excelforum.com/member.php...o&userid=24996
View this thread: http://www.excelforum.com/showthread...hreadid=385228



  #3   Report Post  
N Harkawat
 
Posts: n/a
Default

The place where you have the weekly total instead of using =SUM funciton to
compute the weekly total use
=Subtotal(9,A1:a5)
for all the Weekly totals in your sheet

Then for Daily total at the end use
=subtotal(9,A1:a9999)
and for the daily average
=subtotal(1,a1:a9999)



"dagger" wrote in
message ...

I am curious if anyone can help me out. I am trying to create both an
average and a sum formula that will limit or exclude certain fields.
Here is an example of what I'm working with:

Day1: 25
Day2: 20
Day3: 20
Day4: 25
Day5: 25
Weekly Total: 115

Day1: 15
Day2: 20
Day3: 30
Day4: 35
Day5: 25
Weekly Total: 125

Daily average= ?
Daily Total= ?

Is there any way to run an average excluding the weekly total fields
either by color or any other way. There is a SUM formula in the weekly
total field, can formula fields be excluded? The same for the average
of the daily numbers?

Thanks for any help you can provide.
Todd


--
dagger
------------------------------------------------------------------------
dagger's Profile:
http://www.excelforum.com/member.php...o&userid=24996
View this thread: http://www.excelforum.com/showthread...hreadid=385228



  #4   Report Post  
swatsp0p
 
Posts: n/a
Default


You can use SUMIF and COUNTIF to find your averages, as such:

Assuming your data is in A1:B13 use this formula to find the Average:

=SUMIF(A1:A13,"=DAY*",(B1:B13))/COUNTIF(A1:A13,"=DAY*")

and this give the total of all of the days:

=SUMIF(A1:A13,"=DAY*",(B1:B13))

Of course modify your ranges to meet your needs.

Does this work for you?


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=385228

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On



All times are GMT +1. The time now is 10:21 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"