Formula not working properly
 

D E

2 206
3 172
4 111
5 55 "YES" (in red) or "NO" (in blue)

GOAL: Average the differences from the past 3 rows and compare to the inputed value in D5. If the average is less than D5 the display "YES"(RED), or "NO"(BLUE) in E5.

This is the formula as it is now...
=IF(((D2-D3)+(D3-D4)+(D4-D5)/3)<D5,"YES", "NO")

Calculations:
206-172 = 34, 172-111 = 61, 111-50 = 56... 34+61+56/3 = 50

The formula above (in E5) comes back with "NO" - it should be "YES, being that 50 is < 55

Is it possible to have YES come back in RED and NO in BLUE?... =90 or something like that I think, but not sure where to put that. PLEASE HELP!!

Formula not working properly
 

Hi,

Am Mon, 6 Jan 2014 18:21:00 +0000 schrieb HopsBarley:

This is the formula as it is now...
=IF(((D2-D3)+(D3-D4)+(D4-D5)/3)<D5,"YES", "NO")

your formula is missing a pair of brackets. Try:
=IF((((D2-D3)+(D3-D4)+(D4-D5))/3)<D5,"YES", "NO")
Or a bit shorter but a array formula:
=IF(AVERAGE(D2:D4-D3:D5)<D5,"YES","NO")
Array enter the formula with CTRL+Shift+Enter


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Formula not working properly
 

On Mon, 6 Jan 2014 18:21:00 +0000, HopsBarley wrote:


D E

2 206
3 172
4 111
5 55 "YES" (in red) or "NO" (in blue)

GOAL: Average the differences from the past 3 rows and compare to the
inputed value in D5. If the average is less than D5 the display
"YES"(RED), or "NO"(BLUE) in E5.

This is the formula as it is now...
=IF(((D2-D3)+(D3-D4)+(D4-D5)/3)<D5,"YES", "NO")

Calculations:
206-172 = 34, 172-111 = 61, 111-50 = 56... 34+61+56/3 = 50

The formula above (in E5) comes back with "NO" - it should be "YES,
being that 50 is < 55

Is it possible to have YES come back in RED and NO in BLUE?... =90 or
something like that I think, but not sure where to put that. PLEASE
HELP!!

You can use Conditional Formatting to "color" your answer appropriately.