+ Reply to Thread
Results 1 to 10 of 10

'AdvantageIfs' using multiple criteria returning: #DIV/0!. Help with ignoring blanks+0's

  1. 07-15-2013, 06:45 AM #1
    shadypops
    shadypops is offline
    Registered User
    Join Date
    12-12-2012
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    19

    Question 'AdvantageIfs' using multiple criteria returning: #DIV/0!. Help with ignoring blanks+0's

    Good day excellent excellians!

    I am having an issue in the workbook I have attached Averages Issues Example.xlsx. I have stripped away all irrelevant data, and the formula in question is apparent in sheet entitled 'Table'.

    Basically, I am creating a table displaying various stats, including averages for fundraisers fundraising figures, based on using multiple criteria of: by name, and for days 'Friday' and 'Saturday' only, but am getting a #DIV/0! error.

    I have tried to include criteria for ignoring 0's: '<0', which works fine when the criteria is only based on one day, but when I try to include two days I get an error.

    I am trying to find the average based on only Friday+Saturday, for 'Team Revenue', and 'Sub-Total', as well as for individual names.


    Note:
    -the zeros cascading down the columns after the data in 'Accounts (List)' is a by product of the data being read from continuous new entries from another sheet (which is not included). I have included the example formulas used to read said data in row 18 (which can be seen by the REF! error for this instance).
    -Team revenue is the sum of 2 individual fundraisers 'Sub-Total' (as they work in teams of 2). Which is why there is an empt cell in every other row, and the need to ignore blank cells and zeros.

    I have struggled with this for a while now and my research to find a solution has frazzled me. I am not the best on excel and working out criteria, ranges and similar formulas has been a challenge in itself (although rewarding). I hope I have explained this clearly enough and if anyone could help it would be greatly appreciated! I hope to learn from the solution.

    Thanks!
    Reply With QuoteRegister To Reply
  2. 07-15-2013, 07:35 AM #2
    Fotis1991
    Fotis1991 is offline
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: 'AdvantageIfs' using multiple criteria returning: #DIV/0!. Help with ignoring blanks+

    Do you mean?

    =AVERAGEIFS('Accounts (List)'!V:V,'Accounts (List)'!V:V,"<>0",'Accounts (List)'!B:B,"Friday")+AVERAGEIFS('Accounts (List)'!V:V,'Accounts (List)'!V:V,"<>0",'Accounts (List)'!B:B,"Saturday")
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.
    Reply With QuoteRegister To Reply
  3. 07-15-2013, 07:56 AM #3
    shadypops
    shadypops is offline
    Registered User
    Join Date
    12-12-2012
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: 'AdvantageIfs' using multiple criteria returning: #DIV/0!. Help with ignoring blanks+

    Fotis1991 you are a godsend!

    All I had to do was enclose the whole formula in another set of brackets and /2 to get the desired answer.

    Thank you so much for your response, you have helped me to see the correct method to execute said formula. Thank you!!!
    Reply With QuoteRegister To Reply
  4. 07-15-2013, 07:59 AM #4
    Fotis1991
    Fotis1991 is offline
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: 'AdvantageIfs' using multiple criteria returning: #DIV/0!. Help with ignoring blanks+

    You are welcome and thanks for the feed back.

    As that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thank you.
    Reply With QuoteRegister To Reply
  5. 07-15-2013, 08:44 AM #5
    shadypops
    shadypops is offline
    Registered User
    Join Date
    12-12-2012
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    19

    Question Re: 'AdvantageIfs' using multiple criteria returning: #DIV/0!. Help with ignoring blanks+

    Man, im still having trouble applying an adapted formula to cells q5 downwards.

    I have adapted the previous formula to use names as an additional criteria to find individual named subtotal.

    Here is the formula I have attempted to use:

    (In q5 cascading down):
    =(AVERAGEIFS('Accounts (List)'!U:U,'Accounts (List)'!I:I,A5,'Accounts (List)'!U:U,"<>0",'Accounts (List)'!B:B,"Friday")+AVERAGEIFS('Accounts (List)'!U:U,'Accounts (List)'!I:I,A5,'Accounts (List)'!U:U,"<>0",'Accounts (List)'!B:B,"Saturday"))/2

    The problem I get is a #DIV/0 for Max. This is because he has worked a saturday but not a friday shift. So I need the formula to ignore if there is not an entry for friday and/or saturday afterall.

    Another thing that has puzzled me is the fact that the average for Ali in column Q produces a different result when it should be exactly the same as column P as he has not worked on a sunday or monday...

    Some more insight would be really appreciated.

    Here is the updated workbook:Averages Issues Example.xlsx
    Last edited by shadypops; 07-15-2013 at 08:46 AM. Reason: clarification
    Reply With QuoteRegister To Reply
  6. 07-15-2013, 08:57 AM #6
    Fotis1991
    Fotis1991 is offline
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: 'AdvantageIfs' using multiple criteria returning: #DIV/0!. Help with ignoring blanks+

    Perhaps..

    =IFERROR(IFERROR(IFERROR((AVERAGEIFS('Accounts (List)'!U:U;'Accounts (List)'!I:I;A5;'Accounts (List)'!U:U;"<>0";'Accounts (List)'!B:B;"Friday")+AVERAGEIFS('Accounts (List)'!U:U;'Accounts (List)'!I:I;A5;'Accounts (List)'!U:U;"<>0";'Accounts (List)'!B:B;"Saturday"))/2;AVERAGEIFS('Accounts (List)'!U:U;'Accounts (List)'!I:I;A5;'Accounts (List)'!U:U;"<>0";'Accounts (List)'!B:B;"Friday"));AVERAGEIFS('Accounts (List)'!U:U;'Accounts (List)'!I:I;A5;'Accounts (List)'!U:U;"<>0";'Accounts (List)'!B:B;"Saturday"));"")
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  7. 07-15-2013, 09:43 AM #7
    shadypops
    shadypops is offline
    Registered User
    Join Date
    12-12-2012
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: 'AdvantageIfs' using multiple criteria returning: #DIV/0!. Help with ignoring blanks+

    Wow, that is amazing!! That goes way over my head! You are certainly some sort of excel genius.

    I don't suppose you had any thoughts on why the return for Ali is different than his actual average? Everyone else's seems to be correct except for his!
    Reply With QuoteRegister To Reply
  8. 07-15-2013, 10:58 AM #8
    shadypops
    shadypops is offline
    Registered User
    Join Date
    12-12-2012
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: 'AdvantageIfs' using multiple criteria returning: #DIV/0!. Help with ignoring blanks+

    I think I know why the difference with the averages occurs.

    The formula is adding together the average of the Friday with the average of the saturday, then dividing by 2.

    what is needed, is the average of all of the friday and saturday shifts together.

    Man I am already lost on this one. Any help would be appreciated!
    Reply With QuoteRegister To Reply
  9. 07-15-2013, 11:30 AM #9
    Fotis1991
    Fotis1991 is offline
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: 'AdvantageIfs' using multiple criteria returning: #DIV/0!. Help with ignoring blanks+

    Ali has 1 Saturday and 1 Friday...

    In any case i think this is better way...

    =IFERROR((SUMIFS('Accounts (List)'!U:U;'Accounts (List)'!I:I;A5;'Accounts (List)'!U:U;"<>0";'Accounts (List)'!B:B;"Friday")+SUMIFS('Accounts (List)'!U:U;'Accounts (List)'!I:I;A5;'Accounts (List)'!U:U;"<>0";'Accounts (List)'!B:B;"Saturday"))/(COUNTIFS('Accounts (List)'!B:B;"Saturday";'Accounts (List)'!I:I;A5)+COUNTIFS('Accounts (List)'!B:B;"Friday";'Accounts (List)'!I:I;A5));"")
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  10. 07-15-2013, 04:12 PM #10
    shadypops
    shadypops is offline
    Registered User
    Join Date
    12-12-2012
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: 'AdvantageIfs' using multiple criteria returning: #DIV/0!. Help with ignoring blanks+

    Thanks man you are a diamond
    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)

Similar Threads

  1. [SOLVED] Dividing list into quintiles, returning a value but ignoring blanks.
    By maxfiesta in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-06-2013, 12:42 PM
  2. [SOLVED] How to count cell value with multiple criteria while ignoring duplicate
    By jomi9501 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 07-17-2012, 12:23 AM
  3. [SOLVED] max if multiple conditions, ignoring blanks
    By robotlust in forum Excel General
    Replies: 6
    Last Post: 05-29-2012, 07:38 PM
  4. [SOLVED] Summation of values based on multiple criteria & ignoring blanks
    By coreyjo in forum Excel General
    Replies: 6
    Last Post: 04-18-2012, 04:16 PM
  5. Multiple IF statements plus ignoring blanks
    By dnewby in forum Excel General
    Replies: 2
    Last Post: 10-31-2011, 12:55 PM

Tags for this Thread

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