DAVERAGE using two criteria, one is a date

[mrpaw]

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.

 

[mrpaw]

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".

 

[Marcelo Branco]

The criteria header in J1 must be identical to the field name, that is: SAMPLED_DATE

M.

 

[mrpaw]

Thank you Marcello but making that change did not help.

 

[Marcelo Branco]

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

M.

 

[mrpaw]

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]

 

[AhoyNC]

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
	A	B	C	D	E
1		TimeStamp	TimeStamp
2	Look up Date	>1/18/2017		21.157	1.230333
3
4		TimeStamp	0830 Tank_N	Tank_SP_GR
5		3/18/2017 21:00	26.88	1.283
6		3/18/2017 9:00	27.66	1.266
7		2/8/2017 9:36	26.64	1.278
8		2/8/2017 7:36	26.34	1.27
9		2/7/2017 21:00	26.64	1.278
10		2/7/2017 13:00	24.87	1.27
11		1/19/2017 16:48	25.17	1.263
12		1/19/2017 14:48	24.87	1.278
13		1/18/2017 21:00	25.17	1.263
14		1/18/2017 19:00	19	1.278
15		1/18/2017 9:00	19.3	1.15
Sheet

 

[mrpaw]

Perfection. I never though about the date/time issue. Thank you for posting.

Thank you from eastern NC - like the state.

 

[AhoyNC]

You're welcome.
You may want to change the criteria in B2 of the above example to "greater than" and "equal" to date.

[COLOR=#ff0000]>=[/COLOR]1/18/2017

I lived in Wilmington, NC for awhile.