Hi Everyone,
So I need a formula that allows me to find the average of a column within a chart while ignoring duplicate values and blank cells.
I tried using this formula:
=AVERAGE(IF(FREQUENCY([$AMOUNT],[$AMOUNT]),[$AMOUNT]))
but it doesn't work if there are blank cells present.
Can anyone help?
Thanks
Maybe a better solution exists, but using your formula as a basis:
=AVERAGE(IF(FREQUENCY(IF(ISBLANK(array),MAX(array),array),array),IF(ISBLANK(array),MAX(array),array)))
Enter as an array formula (Ctrl-Shift-Enter)
Pauley
--------
If I helped with your issue, I'd appreciate a rep bump (hit the '*' icon to the bottom left of this post).
Hi,
I tried the formula you suggested and it is giving me an error "#N/A"
Did you enter it properly as an array formula?
Array formulae are confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
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. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
Also, I used 'array' as a placeholder. You would replace with [$AMOUNT].
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks