Use a conditional value in a formula?

Copper Contributor

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
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)

@Cyn_TechnoMom 

=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.

average.png

Thanks so much! I'll try that right away.