Hi
I thought this would be simple but its taking me forever! I have three values that I want to calculate the average for. However they are in a row that has other data in it and some have zero value. So in the example (attached) could someone tell me how to calculate the average which should be '2' but is coming out as 1.33333 etc?
Many thanks
Try this formula
=SUM(B2,D2,F2)/MAX(1,(B2>0)+(D2>0)+(F2>0))
Audere est facere
Hi
Many thanks - i can see it works in the example I gave but I transfered it to the workbook and it has returned the value of two again. The only difference between my workbook and the example I gave is that there is a linked blank cell in B2.......I therefore deleted the value in the example of B2 thinking it would return a value of 2 but it still seemed to work correctly - any ideas and why isnt anything simple!
regards
Hi
Many thanks - i can see it works in the example I gave but I transfered it to the workbook and it has returned the value of two again. The only difference between my workbook and the example I gave is that there is a linked blank cell in B2.......I therefore deleted the value in the example of B2 thinking it would return a value of 2 but it still seemed to work correctly - any ideas and why isnt anything simple!
regards
Maybe if you could provide a spreadsheet with a more accurate characteristics of your problem, we should be able to find a suitable solution for it.
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
Try this and see if it works for you
=SUM(B2,D2,F2)/SUMPRODUCT(($A$1:$F$1="Average grade")*(--(A2:F2>0)))
Hi
Thanks for the reply - in the attached I have put the given formula in column P and the formula I was using in column 'O'. If you look at row 2 you can see my formula working correctly but the given formula not. If I enter a 0 value in F2 the given formula works and my one doesn't! There are similar scenarios for row 4 and row 9. I need the formula to work whether or not there is a blank or a zero value in the relevant cell. Hope you get what I mean and you can solve for me!
best regards
Unless the grades being averaged have equal base values from which the averages are obtained, the average of averages is not worth calculating. There appears to be a too much missing from the problem in order to solve it as it stands.
A posting of what you are actually working with would be of value.
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
The formula in post #6 works just fine. For the new layout I just adjusted range. See formula below:
Corrected formula: For your settings, because values populated from connected workbook the last argument should be not >0 but <>""
=IFERROR(SUM(F2,I2,L2)/SUMPRODUCT(($F$1:$L$1="Average Grade")*(--(F2:L2<>""))),0)
Last edited by AlKey; 01-05-2014 at 11:49 AM.
To ignore blanks and zeroes but avoid errors you can use this formula
=SUM(F2,I2,L2)/MAX(1,INDEX(FREQUENCY((F2,I2,L2),0),2))
...or perhaps simpler with AVERAGEIFS
=IFERROR(AVERAGEIFS(F2:L2,F$1:L$1,"Average Grade",F2:L2,">0"),0)
Last edited by daddylonglegs; 01-05-2014 at 11:58 AM.
resolved - post closed thanks
You're welcome. Don't forget to thank those who helped by clicking on Add Reputation * and please mark thread as "Solved" if your issue has been resolved. (Selecting Thread Tools-> Mark thread as Solved).
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks