Averaging only negative number in every other cell

[BPMike]

Hi,

I have a data set which includes positive and negative numbers. I am able to get my averages for every other cell using =AVERAGE(IF(ISEVEN(COLUMN(G4:DQ4))*(G4:DQ4<>""),G4:DQ4)) but I only need to average negative numbers and can't figure out how I should modify my formula.

Any help would be appreciated

Cheers,

Mike

 

[BPMike]

Forgot to upload a picture.

 

[Georgiboy]

Welcome to MrExcel,

Looking at your current formula, maybe:

=LET(r,G4:DQ4,AVERAGE(IF(ISEVEN(COLUMN(r))*(r<>"")*(r<0),r)))

 

[Joe4]

Welcome to the Board!

Here is one way:

=SUMPRODUCT(--(ISEVEN(COLUMN(G4:DQ4))),--(G4:DQ4<0),G4:DQ4)/SUMPRODUCT(--(ISEVEN(COLUMN(G4:DQ4))),--(G4:DQ4<0))

 

[DanteAmor]

Hi @BPMike .
Welcome to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.

How about

=AVERAGEIF(G4:DQ4,"<0")

 

[Joe4]

How about

=AVERAGEIF(G4:DQ4,"<0")

Dante,
I think you may have missed the part of "every other cell".
It looks like they just want to include values in even numbered columns.

 

[Domenic]

Here's another way...

=AVERAGE(IF(ISEVEN(COLUMN(G4:DQ4)),IF(G4:DQ4<0,G4:DQ4)))

Hope this helps!

 

[DanteAmor]

I think you may have missed the part of "every other cell".
It looks like they just want to include values in even numbered columns.

Hi Joe, you're right, I missed that part.

Here another proposal

varios 12may2023.xlsm
	E
4	-3
Hoja6

Cell Formulas
Range	Formula
E4	E4	=SUM(IF(MOD(COLUMN(G4:Q4),2)=0,IF(G4:Q4<0,G4:Q4)))/COUNT(IF(MOD(COLUMN(G4:Q4),2)=0,IF(G4:Q4<0,1)))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[BPMike]

Here's another way...

=AVERAGE(IF(ISEVEN(COLUMN(G4:DQ4)),IF(G4:DQ4<0,G4:DQ4)))

Hope this helps!

That worked perfectly. Thank you

And thanks everyone for the warm welcome