DAVERAGE using two criteria, one is a date

mrpaw

Board Regular
Joined
Jan 31, 2011
Messages
80
I have a named range table "LabData2" with >30000 lines of analyses with multiple sample names and multiple samples per day for a 1 year period.


Table column headers

ColA ColB ColC ColD ColE
[TABLE="width: 764"]
<tbody>[TR]
[TD]SAMPLE_TITLE
[/TD]
[TD]COMPONENT_NAME
[/TD]
[TD]SAMPLED_DATE
[/TD]
[TD]RESULT_TEXT
[/TD]
[TD]SAMPLE
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Sample = SAMPLE_TITLE Space COMPENENT_NAME

I want to capture the RESUL_TEXT (col D) based on the SAMPLE (col E) and Date, cell $A$1.

Formula doesn’t want to work, keep getting a #DIV/0 error.

=if(DAVERAGE(LabData2,$D$8,I1:J2),I6,DAVERAGE(LabData2,$D$8,I1:J2)) where

I1 = SAMPLE
I2 = 1CP65C AES (Sample name)
J1 = DATE
J2 = value from cell A1

I6 is the cell value to use if there is not a sample for that day.
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
I have a named range table "LabData2" with >30000 lines of analyses with multiple sample names and multiple samples per day for a 1 year period.


Table column headers

ColA ColB ColC ColD ColE
[TABLE="width: 764"]
<tbody>[TR]
[TD]SAMPLE_TITLE[/TD]
[TD]COMPONENT_NAME[/TD]
[TD]SAMPLED_DATE[/TD]
[TD]RESULT_TEXT[/TD]
[TD]SAMPLE[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Sample = SAMPLE_TITLE Space COMPENENT_NAME

I want to capture the RESUL_TEXT (col D) based on the SAMPLE (col E) and Date, cell $A$1.

Formula doesn’t want to work, keep getting a #DIV/0 error.

=if(DAVERAGE(LabData2,$D$8,I1:J2),I6,DAVERAGE(LabData2,$D$8,I1:J2)) where

I1 = SAMPLE
I2 = 1CP65C AES (Sample name)
J1 = DATE
J2 = value from cell A1

I6 is the cell value to use if there is not a sample for that day.

I neglected to put the ISERROR after =if( part of the formula.

Could the sample time stamp be an issue? It formatted like "m/d/yyyy hh:mm" and my date cell is just "m/d/yy".
 
Upvote 0
The criteria header in J1 must be identical to the field name, that is: SAMPLED_DATE

M.
 
Upvote 0
Try to provide a small data sample (~10 rows) along with criteria range and expected result.

M.
 
Upvote 0
Try to provide a small data sample (~10 rows) along with criteria range and expected result.

M.

[TABLE="width: 570"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]Result should be[/TD]
[TD]Result should be[/TD]
[/TR]
[TR]
[TD]Look up Date[/TD]
[TD="align: right"]1/18/2017 [/TD]
[TD]21.157[/TD]
[TD]1.230[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]TimeStamp[/TD]
[TD]0830 Tank_N[/TD]
[TD] Tank_SP_GR[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]3/18/17 21:00[/TD]
[TD="align: right"]26.880[/TD]
[TD="align: right"]1.283[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]3/18/17 9:00[/TD]
[TD="align: right"]27.660[/TD]
[TD="align: right"]1.266[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]2/8/17 9:36[/TD]
[TD="align: right"]26.640[/TD]
[TD="align: right"]1.278[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]2/8/17 7:36[/TD]
[TD="align: right"]26.340[/TD]
[TD="align: right"]1.270[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]2/7/17 21:00[/TD]
[TD="align: right"]26.640[/TD]
[TD="align: right"]1.278[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]2/7/17 13:00[/TD]
[TD="align: right"]24.870[/TD]
[TD="align: right"]1.270[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]1/19/17 16:48[/TD]
[TD="align: right"]25.170[/TD]
[TD="align: right"]1.263[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]1/19/17 14:48[/TD]
[TD="align: right"]24.870[/TD]
[TD="align: right"]1.278[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]1/18/17 21:00[/TD]
[TD="align: right"]25.170[/TD]
[TD="align: right"]1.263[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]1/18/17 19:00[/TD]
[TD="align: right"]19.000[/TD]
[TD="align: right"]1.278[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]1/18/17 9:00[/TD]
[TD="align: right"]19.300[/TD]
[TD="align: right"]1.150[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Yes the timeStamp is the issue.
Since dates are numeric.
1/18/17 = 42753
1/18/17 21:00 = 42753.875
So, the two will not match.

Try something like below:
Excel Workbook
ABCDE
1TimeStampTimeStamp
2Look up Date>1/18/201721.1571.230333
3
4TimeStamp0830 Tank_NTank_SP_GR
53/18/2017 21:0026.881.283
63/18/2017 9:0027.661.266
72/8/2017 9:3626.641.278
82/8/2017 7:3626.341.27
92/7/2017 21:0026.641.278
102/7/2017 13:0024.871.27
111/19/2017 16:4825.171.263
121/19/2017 14:4824.871.278
131/18/2017 21:0025.171.263
141/18/2017 19:00191.278
151/18/2017 9:0019.31.15
Sheet
 
Upvote 0
Perfection. I never though about the date/time issue. Thank you for posting.

Thank you from eastern NC - like the state.
 
Upvote 0
You're welcome.
You may want to change the criteria in B2 of the above example to "greater than" and "equal" to date.
Code:
[COLOR=#ff0000]>=[/COLOR]1/18/2017

I lived in Wilmington, NC for awhile.
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,175
Members
453,021
Latest member
Justyna P

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