+ Reply to Thread
Results 1 to 26 of 26

Limit or Exclude cells in Average and Sum formula

  1. 07-07-2005, 09:48 AM #1
    dagger
    dagger is offline
    Registered User
    Join Date
    07-07-2005
    Posts
    1

    Question 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
    Reply With QuoteRegister To Reply
  2. 07-07-2005, 10:52 AM #2
    swatsp0p
    swatsp0p is offline
    Forum Expert swatsp0p's Avatar
    Join Date
    10-07-2004
    Location
    Kentucky, USA
    MS-Off Ver
    Excel 2010
    Posts
    1,545
    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?
    Bruce
    The older I get, the better I used to be.
    USA
    Reply With QuoteRegister To Reply
  3. 07-07-2005, 11:05 AM #3
    Bob Phillips
    Guest

    Re: Limit or Exclude cells in Average and Sum formula

    =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" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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
    >


    Reply With QuoteRegister To Reply
  4. 07-07-2005, 11:05 AM #4
    N Harkawat
    Guest

    Re: Limit or Exclude cells in Average and Sum formula

    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" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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
    >


    Reply With QuoteRegister To Reply
  5. 09-05-2005, 10:05 PM #5
    Bob Phillips
    Guest

    Re: Limit or Exclude cells in Average and Sum formula

    =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" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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
    >


    Reply With QuoteRegister To Reply
  6. 09-05-2005, 10:05 PM #6
    N Harkawat
    Guest

    Re: Limit or Exclude cells in Average and Sum formula

    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" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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
    >


    Reply With QuoteRegister To Reply
  7. 09-05-2005, 11:05 PM #7
    Bob Phillips
    Guest

    Re: Limit or Exclude cells in Average and Sum formula

    =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" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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
    >


    Reply With QuoteRegister To Reply
  8. 09-05-2005, 11:05 PM #8
    N Harkawat
    Guest

    Re: Limit or Exclude cells in Average and Sum formula

    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" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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
    >


    Reply With QuoteRegister To Reply
  9. 09-06-2005, 12:05 AM #9
    N Harkawat
    Guest

    Re: Limit or Exclude cells in Average and Sum formula

    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" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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
    >


    Reply With QuoteRegister To Reply
  10. 09-06-2005, 12:05 AM #10
    Bob Phillips
    Guest

    Re: Limit or Exclude cells in Average and Sum formula

    =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" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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
    >


    Reply With QuoteRegister To Reply
  11. 09-06-2005, 01:05 AM #11
    Bob Phillips
    Guest

    Re: Limit or Exclude cells in Average and Sum formula

    =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" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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
    >


    Reply With QuoteRegister To Reply
  12. 09-06-2005, 01:05 AM #12
    N Harkawat
    Guest

    Re: Limit or Exclude cells in Average and Sum formula

    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" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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
    >


    Reply With QuoteRegister To Reply
  13. 09-06-2005, 02:05 AM #13
    Bob Phillips
    Guest

    Re: Limit or Exclude cells in Average and Sum formula

    =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" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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
    >


    Reply With QuoteRegister To Reply
  14. 09-06-2005, 02:05 AM #14
    N Harkawat
    Guest

    Re: Limit or Exclude cells in Average and Sum formula

    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" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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
    >


    Reply With QuoteRegister To Reply
  15. 09-06-2005, 03:05 AM #15
    Bob Phillips
    Guest

    Re: Limit or Exclude cells in Average and Sum formula

    =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" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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
    >


    Reply With QuoteRegister To Reply
  16. 09-06-2005, 03:05 AM #16
    N Harkawat
    Guest

    Re: Limit or Exclude cells in Average and Sum formula

    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" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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
    >


    Reply With QuoteRegister To Reply
  17. 09-06-2005, 04:05 AM #17
    N Harkawat
    Guest

    Re: Limit or Exclude cells in Average and Sum formula

    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" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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
    >


    Reply With QuoteRegister To Reply
  18. 09-06-2005, 04:05 AM #18
    Bob Phillips
    Guest

    Re: Limit or Exclude cells in Average and Sum formula

    =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" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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
    >


    Reply With QuoteRegister To Reply
  19. 09-06-2005, 06:05 AM #19
    Bob Phillips
    Guest

    Re: Limit or Exclude cells in Average and Sum formula

    =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" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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
    >


    Reply With QuoteRegister To Reply
  20. 09-06-2005, 06:05 AM #20
    N Harkawat
    Guest

    Re: Limit or Exclude cells in Average and Sum formula

    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" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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
    >


    Reply With QuoteRegister To Reply
  21. 09-06-2005, 07:05 AM #21
    Bob Phillips
    Guest

    Re: Limit or Exclude cells in Average and Sum formula

    =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" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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
    >


    Reply With QuoteRegister To Reply
  22. 09-06-2005, 07:05 AM #22
    N Harkawat
    Guest

    Re: Limit or Exclude cells in Average and Sum formula

    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" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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
    >


    Reply With QuoteRegister To Reply
  23. 09-06-2005, 10:05 AM #23
    Bob Phillips
    Guest

    Re: Limit or Exclude cells in Average and Sum formula

    =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" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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
    >


    Reply With QuoteRegister To Reply
  24. 09-06-2005, 10:05 AM #24
    N Harkawat
    Guest

    Re: Limit or Exclude cells in Average and Sum formula

    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" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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
    >


    Reply With QuoteRegister To Reply
  25. 09-06-2005, 12:05 PM #25
    Bob Phillips
    Guest

    Re: Limit or Exclude cells in Average and Sum formula

    =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" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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
    >


    Reply With QuoteRegister To Reply
  26. 09-06-2005, 12:05 PM #26
    N Harkawat
    Guest

    Re: Limit or Exclude cells in Average and Sum formula

    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" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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
    >


    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1