How to calculate average only if values are proximate, equal or higher of the average value result?

Lacan

Active Member
Joined
Oct 5, 2016
Messages
253
Office Version
  1. 365
Platform
  1. Windows
Hi Guys, 🖐

Would like your help to discover formula to calculate average only if values are proximate, equal or higher of the average value result?
All the help are very welcome.
Thank you very much! 💪👍👍

Fórmula Principal para o Ficheiro VDF.xlsm
ABCDEFGHIJKLMNOP
1
2NAMESENE. 2025FEB. 2025MAR. 2025ABR. 2025MAY. 2025JUN. 2025JUL. 2025AGO. 2025SEP. 2025OCT. 2025NOV. 2025DIC. 2025AVERAGE RESULT
3JOHN22
4ANNA51005510522
5LISA10435033787410
6MARIE250502321
7ROB123410088817
8
9
AVERAGE
Cell Formulas
RangeFormula
C2:N2C2=TRANSPOSE(UPPER(TEXT(EDATE("01-01-2025",SEQUENCE(12,1,0)),"[$-es-ES]mmm aaa")))
O3:O7O3=AVERAGEIFS(C3:N3,C3:N3,">0")
Dynamic array formulas.
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Something like this?
Note: AVERAGE will ignore blank cells so you don't need the ">0".
Tips2025.xlsx
BCDEFGHIJKLMNO
1
2NAMESENE. MI.FEB. SÁ.MAR. SÁ.ABR. MA.MAY. JU.JUN. DO.JUL. MA.AGO. VI.SEP. LU.OCT. MI.NOV. SÁ.DIC. LU.AVERAGE RESULT
3JOHN22
4ANNA510055105100
5LISA10435033787430
6MARIE250502350
7ROB1234100888100
Sheet6
Cell Formulas
RangeFormula
C2:N2C2=TRANSPOSE(UPPER(TEXT(EDATE("01-01-2025",SEQUENCE(12,1,0)),"[$-es-ES]mmm aaa")))
O3:O7O3=AVERAGEIFS(C3:N3,C3:N3,">="&AVERAGE(C3:N3))
Dynamic array formulas.
 
Upvote 0
Solution
Dear @Cubist

Through your last formula, how to Calculate Medium only values if:

1.If they are medium greater then 10%
2.Between range medium less and medium greather 10%.

Can you please specify one formula for each one.
Thank you very much. 👍👍🍻
 
Upvote 0
I'm not familiar with the term "medium" in this context, do you mean median?
 
Upvote 0
I'm not familiar with the term "medium" in this context, do you mean median?

Sorry @Cubist for the excel translation was trying to say "average" not "medium".
So correcting:

Through your last formula, how to Calculate average only values if:

1.If they are
average greater then 10%
2.Between range
average less and medium greather 10%.

Can you please specify one formula for each one.
Many thanks.🙏🙏
 
Upvote 0
See if Col P and Q are what you're looking for. Note the #DIV/0! error is because no criteria match.
Book1
BCDEFGHIJKLMNOPQ
1
2NAMESENE. MI.FEB. SÁ.MAR. SÁ.ABR. MA.MAY. JU.JUN. DO.JUL. MA.AGO. VI.SEP. LU.OCT. MI.NOV. SÁ.DIC. LU.AVERAGE RESULTAVERAGE RESULT >110%AVERAGE (BETWEEN AVG AND 110% OF AVG)
3JOHN22#DIV/0!2
4ANNA510055105100100#DIV/0!
5LISA104350337874305010
6MARIE25050235050#DIV/0!
7ROB1234100888100100#DIV/0!
Sheet1
Cell Formulas
RangeFormula
C2:N2C2=TRANSPOSE(UPPER(TEXT(EDATE("01-01-2025",SEQUENCE(12,1,0)),"[$-es-ES]mmm aaa")))
O3:O7O3=AVERAGEIFS(C3:N3,C3:N3,">="&AVERAGE(C3:N3))
P3:P7P3=AVERAGEIFS(C3:N3,C3:N3,">="&1.1*AVERAGE(C3:N3))
Q3:Q7Q3=AVERAGEIFS(C3:N3,C3:N3,"<="&1.1*AVERAGE(C3:N3),C3:N3,">="&AVERAGE(C3:N3))
Dynamic array formulas.
 
Upvote 0

Forum statistics

Threads
1,225,897
Messages
6,187,708
Members
453,435
Latest member
U4US

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top