T
tralls
I have a unique lookup formula that I need assistance with. I need t
capture the last entered value under a date column ("Actual" belo
under date 11/1/2004 - 89%) but skip non-Actual columns in betwee
("Conv" below). I then need to determine if this is a trend up, down
or flat; against the average of all "Actual" data entered.
9/1/2004________10/1/2004_________11/1/2004
Actual_ Conv_____Actual_ Conv______Actual_ Conv
90%_ 64%_______85%_ 68%________89%_ 22%
In this example, I would need to consider that the average of al
Actual cells (data values) is 88% and that 89% (11/1/04 Actual) is
trend UP against that average. Ideally, this formula would omit th
last date (11/1/04) but this is acceptable to include.
Here's where I am stuck:
=IF(INDEX(C5:H5,COUNTA(C5:H5))<AVERAGE(C5:H5),"DOWN",IF(INDEX(C5:H5,COUNTA(C5:H5))>AVERAGE(C5:H5),"UP","EVEN"))
I tried to integrate VLOOKUP, but am unsuccessful. Thank you i
advance for your expertise
capture the last entered value under a date column ("Actual" belo
under date 11/1/2004 - 89%) but skip non-Actual columns in betwee
("Conv" below). I then need to determine if this is a trend up, down
or flat; against the average of all "Actual" data entered.
9/1/2004________10/1/2004_________11/1/2004
Actual_ Conv_____Actual_ Conv______Actual_ Conv
90%_ 64%_______85%_ 68%________89%_ 22%
In this example, I would need to consider that the average of al
Actual cells (data values) is 88% and that 89% (11/1/04 Actual) is
trend UP against that average. Ideally, this formula would omit th
last date (11/1/04) but this is acceptable to include.
Here's where I am stuck:
=IF(INDEX(C5:H5,COUNTA(C5:H5))<AVERAGE(C5:H5),"DOWN",IF(INDEX(C5:H5,COUNTA(C5:H5))>AVERAGE(C5:H5),"UP","EVEN"))
I tried to integrate VLOOKUP, but am unsuccessful. Thank you i
advance for your expertise