average only descending values

rickblunt

Well-known Member
Joined
Feb 18, 2008
Messages
609
Office Version
  1. 2019
Platform
  1. Windows
Greetings, I am pondering whether I can average some values using the AVERAGEIF formula. I record tank values on a daily basis, for the sake of this example we will say 0 to a 1000 gallons. Everyday we use between 50 and 100 gallons, and we refill the tank every 4-8 days or so. So the data table would be showing ~6 days of descending values and then 1 ascending value and then repeat. I am not necessarily interested in the exact daily average, just a rough average. The filling frequency cycle could change depending on usage rates so this is what is making it difficult to just make the average a 6 day average or something. The thought occurred to me that I could just RANK all the numbers first and then average them all out, but I don't think that would be proper.

If all the values were in a single column, is there a way to write the AVERAGEIF formula so that it only averages the values where the preceding number for it is higher, but ignores the values in which the preceding one before it is lower?

Appreciate any insight - thanks,
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
If your happy with a helper column you could use

+Fluff.xlsm
EFG
4105.769231
591
681
771
861
951
10100
1161
1251
1341
1421
15100
1681
1771
1851
1931
20100
21
22
Images
Cell Formulas
RangeFormula
G4G4=AVERAGEIF(F4:F100,1,E4:E100)
F5:F20F5=--(E5<E4)
 
Upvote 0
Hi Fluff,
(I am totally cool with help columns, use them all over the place ;)) So you gave each cell a 1 or 0 value and then just told the average to look at the 1's - Genius!
Thank you so much for the knowledge
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,212,938
Messages
6,110,775
Members
448,298
Latest member
carmadgar

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