Formula not working properly

H

HopsBarley

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 th
inputed value in D5. If the average is less than D5 the displa
"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 o
something like that I think, but not sure where to put that. PLEAS
HELP!
 
C

Claus Busch

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")
Click to expand...

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

Ron Rosenfeld

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!!
Click to expand...

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