Define Range based on values for "average" calculation of data in another tab

Lilium

New Member
Joined
Jun 6, 2023
Messages
9
Office Version
  1. 2019
Platform
  1. Windows
I have a table with temperatures from thermometers T1 to T4 taken on different time intervals 'Time" collumn in the "Data" tab. Now I want to calculate the average of all the thermometer data in the table (A2:E24) based on the range from "start time" to the "end time" given in the tab "Analysis".
So, If I fill in or change the "start time" and "end time" in cells B2 and D2 of the "Analysis" tab, the cell A5 should calculate the Average of the "T" values (T1 to T4) in the "Data" tab in the part of the range (A2:E24) from the chosen start time (B2) till the end time (D2).

I can't seem to think of a way of doing this. any help is much appreciated. Thanks!!
 

Attachments

  • test-average2.png
    test-average2.png
    24 KB · Views: 16
  • test-average1.png
    test-average1.png
    38.5 KB · Views: 20

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
There's probably a more elegant solution to this (I couldn't get AverageIfs to work for some reason) but I think this gives the correct answer (NB random temps used)
Book1
ABCD
2Start time:08:33:00End time:08:48:00
3
4
523.4703
Analysis
Cell Formulas
RangeFormula
A5A5=SUMPRODUCT((Data!$A$2:$A$24>=$B$2)*(Data!$A$2:$A$24<=$D$2)*(Data!$B$2:$E$24))/SUMPRODUCT((Data!$A$2:$A$24>=$B$2)*(Data!$A$2:$A$24<=$D$2)*(Data!$B$2:$E$24<>""))
 
Upvote 0
Solution
Thanks Kevin! I don't understand the formula but it works like a charm! Much appreciated. Now I was wondering if I could alter the formula to calculate the MIN and MAX instead of the average? Difficult to say if I dont understand the formula :'). Do you think this is possible?
 
Upvote 0
The formula is basically 2 Sumproducts - the first sums all the values in the time range, the second counts the values in the time range - then it's simply the sum/count to get the average. As far as Min/Max goes, try this:
Book1
ABCD
1
2Start time:08:33:00End time:08:48:00
3
4
523.68071Average
6
722.04322Min
8
924.95149Max
Analysis
Cell Formulas
RangeFormula
A5A5=SUMPRODUCT((Data!$A$2:$A$24>=$B$2)*(Data!$A$2:$A$24<=$D$2)*(Data!$B$2:$E$24))/SUMPRODUCT((Data!$A$2:$A$24>=$B$2)*(Data!$A$2:$A$24<=$D$2)*(Data!$B$2:$E$24<>""))
A7A7=MIN(IF((Data!$A$2:$A$24>=$B$2)*(Data!$A$2:$A$24<=$D$2),Data!$B$2:$E$24))
A9A9=MAX(IF((Data!$A$2:$A$24>=$B$2)*(Data!$A$2:$A$24<=$D$2),Data!$B$2:$E$24))
 
Upvote 0
The formula is basically 2 Sumproducts - the first sums all the values in the time range, the second counts the values in the time range - then it's simply the sum/count to get the average. As far as Min/Max goes, try this:
Book1
ABCD
1
2Start time:08:33:00End time:08:48:00
3
4
523.68071Average
6
722.04322Min
8
924.95149Max
Analysis
Cell Formulas
RangeFormula
A5A5=SUMPRODUCT((Data!$A$2:$A$24>=$B$2)*(Data!$A$2:$A$24<=$D$2)*(Data!$B$2:$E$24))/SUMPRODUCT((Data!$A$2:$A$24>=$B$2)*(Data!$A$2:$A$24<=$D$2)*(Data!$B$2:$E$24<>""))
A7A7=MIN(IF((Data!$A$2:$A$24>=$B$2)*(Data!$A$2:$A$24<=$D$2),Data!$B$2:$E$24))
A9A9=MAX(IF((Data!$A$2:$A$24>=$B$2)*(Data!$A$2:$A$24<=$D$2),Data!$B$2:$E$24))
Thanks Kevin!
 
Upvote 0
Kevin, one last thing if you would like to help me out. In regard of the SUMPRODUCT formula you pasted for Cell A5. Let's say T1 and T3 values are in one area and T2 and T4 values are in an other physical area, and i would like to calculate the average temperatures of each of those areas. Then I wont be able to mention the range Data!$B$2:$E$24 in the SUMPRODUCT formula but I would need a combination of B2:B24 and D2:D24 and another sumproduct formula with C2:C24 and E2:E24. However if I fill in B2:B24;D2:D24 in stead of B2:E24 i get an error.
What am I doing wrong?
 
Upvote 0
You can do this by referencing the headers (e.g. T1 & T3 only) within the Sumproduct formula. Like this:
lilium.xlsx
ABCD
1
2Start time:08:33:00End time:08:48:00
3
4
522.534375Average
6
721.0Min
8
924.9Max
10
11
1222.853125AverageT1 & T3 only
13
1422.215625AverageT2 & T4 only
Analysis
Cell Formulas
RangeFormula
A5A5=SUMPRODUCT((Data!$A$2:$A$24>=$B$2)*(Data!$A$2:$A$24<=$D$2)*(Data!$B$2:$E$24))/SUMPRODUCT((Data!$A$2:$A$24>=$B$2)*(Data!$A$2:$A$24<=$D$2)*(Data!$B$2:$E$24<>""))
A7A7=MIN(IF((Data!$A$2:$A$24>=$B$2)*(Data!$A$2:$A$24<=$D$2),Data!$B$2:$E$24))
A9A9=MAX(IF((Data!$A$2:$A$24>=$B$2)*(Data!$A$2:$A$24<=$D$2),Data!$B$2:$E$24))
A12A12=SUMPRODUCT((Data!$A$2:$A$24>=$B$2)*(Data!$A$2:$A$24<=$D$2)*((Data!$B$1:$E$1="T1")+(Data!$B$1:$E$1="T3"))*(Data!$B$2:$E$24<>""),Data!$B$2:$E$24)/SUMPRODUCT((Data!$A$2:$A$24>=$B$2)*(Data!$A$2:$A$24<=$D$2)*((Data!$B$1:$E$1="T1")+(Data!$B$1:$E$1="T3"))*(Data!$B$2:$E$24<>""))
A14A14=SUMPRODUCT((Data!$A$2:$A$24>=$B$2)*(Data!$A$2:$A$24<=$D$2)*((Data!$B$1:$E$1="T2")+(Data!$B$1:$E$1="T4"))*(Data!$B$2:$E$24<>""),Data!$B$2:$E$24)/SUMPRODUCT((Data!$A$2:$A$24>=$B$2)*(Data!$A$2:$A$24<=$D$2)*((Data!$B$1:$E$1="T2")+(Data!$B$1:$E$1="T4"))*(Data!$B$2:$E$24<>""))
 
Upvote 0

Forum statistics

Threads
1,223,236
Messages
6,170,912
Members
452,366
Latest member
TePunaBloke

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