Hi again,
I have a table of numbers that I am finding the average for, thanks so my last post I have successfully got a formula that works for this table to find the average =AVERAGE (d52,d62,d72,d82), however some of my collumns do not have data in currently and when I add the formula I get #DIV/0!
This data will change over time, so I need to find a formula that excludes the blanks and does not count them as '0'.
Can anyone help?
thank you
Try AVERAGEIFS with a criterion to exclude blanks ("<>"):
https://support.office.com/en-ie/art...8-f7c5c3001690
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Thank you, I tried what you suggested: =Averageifs(D44,D52,D59,D67)(">0") and I get an error saying I have used to many arguments.
Have I done something wrong?
You need to use a range:
=AVERAGEIFS(average_range,criteria_range_1,criteria_1,etc.)
Attach the workbook if you are struggling.
What logic are you trying to apply? Your cells are non-contigeous? If so:
=AVERAGEIF(INDIRECT({"D44","D52","D59","D67"}),">0")
I have tried those formulas, however I am still getting an error.
I have attached the workbook.
You will see I am trying to put the formula in column G.
Step 1- average shows in G7
Step 2- average shows in G15
Step 3- average shows in g22
Step 4- average shows in g30
I would like to ignore '0' and blanks as part of the formula and find the average between those cells that are populated 1-5.
thank you.
I think getting the cell references right might help!
=AVERAGEIF(INDIRECT({"D7","D15","D22","D30"}),">0")
However, I am not clear what exactly you are attempting. Should the value in G7 be an average of just Step 1 values? Or something else?
You could use this for Step 1:
=AVERAGEIFS($C$3:$C$31,$C$3:$C$31,">0",$A$3:$A$31,"Group Status")
The cell references were correct previously, I had to copy into a separate workbook to get rid of personal data.
Yes G7 shows an average of Step 1 values
G15, an average of Step 2 values.
Try this in G7 drag copied down:
=IF(A7="","",SUMPRODUCT($C$3:$F$31*($A$3:$A$31="Group Status")*(TRIM($C$2:$F$2)=H7))/SUMPRODUCT(($C$3:$F$31>0)*($A$3:$A$31="Group Status")*(TRIM($C$2:$F$2)=H7)))
Thank you. DO you know how I get it to update automatically, without having to click within the formula?
I tried Refreshing All Data and that didn't work.
thanks for your help.
It sounds as if you have switched calculations to manual - it should update without intervention. Check on the Formulas ribbon - Calculation Options.
Ignore my previous message, I have worked out how to do it :-) thank you.
No worries!
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks