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

SAMPLE_TITLE

COMPONENT_NAME

SAMPLED_DATE

RESULT_TEXT

SAMPLE

<tbody>
</tbody>

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

SAMPLE_TITLE

COMPONENT_NAME

SAMPLED_DATE

RESULT_TEXT

SAMPLE

<tbody>
</tbody>

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.

		Result should be	Result should be
Look up Date	1/18/2017	21.157	1.230
	TimeStamp	0830 Tank_N	Tank_SP_GR
	3/18/17 21:00	26.880	1.283
	3/18/17 9:00	27.660	1.266
	2/8/17 9:36	26.640	1.278
	2/8/17 7:36	26.340	1.270
	2/7/17 21:00	26.640	1.278
	2/7/17 13:00	24.870	1.270
	1/19/17 16:48	25.170	1.263
	1/19/17 14:48	24.870	1.278
	1/18/17 21:00	25.170	1.263
	1/18/17 19:00	19.000	1.278
	1/18/17 9:00	19.300	1.150

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

 

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