I comment on RUclips sparingly, but friend, I have to let you know I greatly appreciate this. You explained this perfectly and it's a great trick around a frustrating issue.
This is great when values are on the same sheet. This seems to not work when averaging values from different sheets. How can I average Non-contiguous different sheet values?
Hello, please advise how to use this formula if I need to calculate average not in range but in many separate cells, I see the mistake: "You've entered to many arguments for this formula"
its a great function but "averageif" imo needs a little work... using it with other functions can sometimes result in an DIV/0 error even when there are no zeros...
Just came across using AVERAGEIF. It is frustrating as its range argument can only take the selected cells not any cells that are calculated by some formula.
this is truly helpful steve. i have a complicated averageifs project i am working on and i'm stuck with errors. i hope u can find the time to take a look at it. can i email worksheet to u?
i'm doing non-contiguous ranges and its being answered on your video tutorial. however, how do you do multiple averageifs in a single formula or is it possible?
hi excel newbie here. i have problem on getting an average value in a column where it came from formulated function. say, cell d5 to d10 are the results from an IF function, then when i'll use AVERAGE function to those cells i get div/o error.. any help is greatly appreciated. thanks
Hello, I'm trying to use this formula in google sheets, and I can't seem to make it work. Here is my formula: =SUM(E23,E32,E14)/INDEX(FREQUENCY((E23,E32,E14),0),2) Did I mess something up, or is it Google sheets?
The index-frequency formula works great, but if they are all zeros or blank to start with (to be populated at a later time and/or from a different tab), I get a "#DIV/0!" error. Is there a way around the division by zero error?
Hi Sujatha, it depends on your Excel version. If you have version 2010 and later, use the IFERROR() function. =iferror('your function here', 'value to return if your function shows a Div0# error")
Hello exceldashbord...i have a problème... in m'y excel file i should full fill every day an opération daily report '' day 1 to 30 ''...in thé end of the month i should deliver à monthly report in sheet ''N'' resuming every task performance...Please how Can i do it ...Please help.
Hi Matt, it didn't work for me. I got the same answer as that did not ignore zero. Do you have a sample formula? I tried all options and all the same result.
I comment on RUclips sparingly, but friend, I have to let you know I greatly appreciate this. You explained this perfectly and it's a great trick around a frustrating issue.
Thank you soooo much for this video. I searched so many videos and finally......this. Brilliant! Genius!!
You are very welcome. So glad to help
This was exactly what I was looking for. Greatly appreciated. Thank you!
Awesome, thanks for the feedback
awesome i was searching for this for long time, thankyouuuuuuuuu verrrrrrrry muuuuuuuch
Thanks for the AWESOOMMMME reply :)
Thank You For This Video 👌👍
Problem Solved ☺️
You are really great. Thank you!
Happy to help! Thanks for the comment. It helps!
This is great when values are on the same sheet. This seems to not work when averaging values from different sheets. How can I average Non-contiguous different sheet values?
Dude, THANK YOU!! ..liked and subscribed.
Thanks, that is awesome!
Thank you for your video. The frequency formula is considering the negative numbers as zero as well. Any suggestions? Thanks
Hello, please advise how to use this formula if I need to calculate average not in range but in many separate cells, I see the mistake: "You've entered to many arguments for this formula"
AVERAGEIF gives errors. I started using simple AVERAGE with FILTER. Works for me.
thanks boss
Welcome and thanks for your great comment!
awesome
Thx
its a great function but "averageif" imo needs a little work... using it with other functions can sometimes result in an DIV/0 error even when there are no zeros...
Just came across using AVERAGEIF. It is frustrating as its range argument can only take the selected cells not any cells that are calculated by some formula.
this is truly helpful steve. i have a complicated averageifs project i am working on and i'm stuck with errors. i hope u can find the time to take a look at it. can i email worksheet to u?
Hi ding, you can send me a contact request from the website: exceldashboardtemplates.com and I can see.
hi, this is working in excel, but it's not working in google sheets, don't know why (same data range)
Does anyone know how to do it on google sheets? (Also I have to use 12 cells, not sure if it's too much) Thanks a lot!
i'm doing non-contiguous ranges and its being answered on your video tutorial. however, how do you do multiple averageifs in a single formula or is it possible?
Hi ding, it depends on what you are trying to do. the IFS statement already means multiple, so I am confused.
hi excel newbie here. i have problem on getting an average value in a column where it came from formulated function. say, cell d5 to d10 are the results from an IF function, then when i'll use AVERAGE function to those cells i get div/o error.. any help is greatly appreciated. thanks
Hi Mr. Bean, sounds like you have a bad value in your range.
Hello, I'm trying to use this formula in google sheets, and I can't seem to make it work. Here is my formula: =SUM(E23,E32,E14)/INDEX(FREQUENCY((E23,E32,E14),0),2) Did I mess something up, or is it Google sheets?
Hi Trevor, have you tried each component separately and do they all work?
@@exceldashboards I have the same questions that didn't work on google sheets. Would you know how to use it there? Thanks a lot!
The index-frequency formula works great, but if they are all zeros or blank to start with (to be populated at a later time and/or from a different tab), I get a "#DIV/0!" error. Is there a way around the division by zero error?
Hi Sujatha, it depends on your Excel version. If you have version 2010 and later, use the IFERROR() function. =iferror('your function here', 'value to return if your function shows a Div0# error")
That works perfectly! Thanks a ton!
Wonderful, thanks for letting me know that this video helped.
Can you do a AVERAGEIFS tutorial that ignores #DIV/0! ?
Can you please tell me if i want to exclude 1 in place of zero and blank in the same situation, then what should i do. Reply me please.
Hi Ram, i would change the operator in the formula from a value from ">0" to ">1" to fix your question.
Hello exceldashbord...i have a problème... in m'y excel file i should full fill every day an opération daily report '' day 1 to 30 ''...in thé end of the month i should deliver à monthly report in sheet ''N'' resuming every task performance...Please how Can i do it ...Please help.
Hi there. You can use the Sum function as follows: =SUM([SheetA]:SheetM]!A2) but not sure I understand your question.
But value 0 in all colomn so return #div/0. But I want return 0. Please help
you can use a IFError formula to catch those errors and set it to 0 so you can chart it.
Can't you just use Aggregate function instead ?
Hi Matt, it didn't work for me. I got the same answer as that did not ignore zero. Do you have a sample formula? I tried all options and all the same result.
Averageif(e2:e5;c2:c5;g2) =???
Hi AddreyThe, are you proposing this as a soution or is this a question? Thanks!
Excel Dashboard Templates oh sory that is question