I'm trying to use the average if function to average a range of numbers, only if a different range of numbers numbers equal a number in two different cells.
ex. average A1:F1 if G1:L1 equals M1 or M2
I'm trying to use the average if function to average a range of numbers, only if a different range of numbers numbers equal a number in two different cells.
ex. average A1:F1 if G1:L1 equals M1 or M2
Last edited by kgbxjester; 09-15-2009 at 03:15 PM.
Try
=AVERAGE(IF((G1:L1="M1")+(G1:L1="M2"),A1:F1))
confirmed with CTRL+SHIFT+ENTER not just ENTER
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
or
=SUMPRODUCT(($G$1:$L$1=$M$1)+($G$1:$L$1=$M$2),($A$1:$F$1))/SUMPRODUCT(($G$1:$L$1=$M$1)+($G$1:$L$1=$M$2))
normally entered
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
I think the sumproduct formula worked...still working through the data to verify. thank you
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks