Forum Discussion
Use a conditional value in a formula?
I need to create a formula to get the average of 11 discontiguous cells. If there's a negative value in one of those cells, though, it should be dropped.
Is this possible? Can it be done without using VBA? If I need to use VBA, what's the best resource for getting started?
For context, I have run and (lightly) edited macros created by someone else, but I've never created one myself. I have some experience with Python, but I am not a programmer. I'm a longtime Excel user (back to MS-DOS days), but I haven't been using it heavily in the last few years.
Thank you!
3 Replies
=AVERAGE(FILTER(TOCOL(A2:E13,1),TOCOL(A2:E13,1)>0))
With Office 365 or Excel for the web this formula works in my example.
- Thanks so much! I'll try that right away.
- Why are those cells non-contiguous? Can you show us what your layout looks like? Or -even better- upload an anonymized copy of your workbook? (to upload files, click the reply link and then the "Open full text editor" link)
