IF Function

A

ADE2

Hi

I have the following IF function that contains 6 nested IF functions
based on the slope of a Moving Average,each IF function scores a
certain number of points based on the slope of the Moving Average.




=IF(AND(AF18="UP",AH18="UP"),12,IF(AND(AF18="DOWN",AH18="UP"),9,IF(AND(AF18="UP",AH18="DOWN"),6,IF(AND(AF18="DOWN",AH18="DOWN"),3,IF(AND(AF18="FLAT",AH18="DOWN"),6,IF(AND(AF18="FLAT",AH18="UP"),9,))))))


I know that the limit is 7 nested IF functions.

I have the following three IF functions to add which will take me over
the limit for nested IF functions as i can only incorporate one more
function.

Is it possible because the final three functions all score the same
number of points(7.5),to make the final IF functions say if cells AF18
and AH18 are anything other than the the outcome of the first six IF
functions that the score would be 7.5

=IF(AND(AF18="UP",AH18="FLAT"),7.5,IF(AND(AF18="DOWN",AH18="FLAT"),7.5,IF(AND(AF18="FLAT",AH18="FLAT"),7.5)))

Thanks for the help

Ade
 
P

Peo Sjoblom

=INDEX({12;9;6;3;6;9;7.5;7.5;7.5},MATCH(1,({"UP";"DOWN";"UP";"DOWN";"FLAT";"
FLAT";"UP";"DOWN";"FLAT"}=AF18)*({"UP";"UP";"DOWN";"DOWN";"DOWN";"UP";"FLAT"
;"FLAT";"FLAT"}=AH18),0))

entered with ctrl + shift & enter

or this formula entered normally

=SUMPRODUCT(--({"UP";"DOWN";"UP";"DOWN";"FLAT";"FLAT";"UP";"DOWN";"FLAT"}=AF
18),--({"UP";"UP";"DOWN";"DOWN";"DOWN";"UP";"FLAT";"FLAT";"FLAT"}=AH18),{12;
9;6;3;6;9;7.5;7.5;7.5})

of course if you create a table looking like

UP UP 12
DOWN UP 9
UP DOWN 6
DOWN DOWN 3
FLAT DOWN 6
FLAT UP 9
UP FLAT 7.5
DOWN FLAT 7.5
FLAT FLAT 7.5

or something off the vies, insert>name>defined and give it a name like My
Table
then you can shorten the formula to


=SUMPRODUCT(--(INDEX(MyTable,,1)=AF18),--(INDEX(MyTable,,2)=AH18),INDEX(MyTa
ble,,3))

--


For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom
 
S

Soo Cheon Jheong

Ade! Try:


=VLOOKUP(AF18&AH18,"UPUP",12;"UPDOWN",6;"UPFLAT",7.5;"DOWNUP",9;"DOWNDOWN",

3;"DOWNFLAT",7.5;"FLATUP",9;"FLATDOWN",6;"FLATFLAT",7.5},2,FALSE)


--
Regards,
Soo Cheon Jheong
Seoul, Korea
_ _
^¢¯^
--
 
A

Arvi Laanemets

Hi

The third way (not very elegant, but very easy to expand)

=AND(AF18="UP",AH18="UP")*12+AND(AF18="DOWN",AH18="UP")*9+AND(AF18="UP",AH18
="DOWN")*6+AND(AF18="DOWN",AH18="DOWN")*3+AND(AF18="FLAT",AH18="DOWN")*6+AND
(AF18="FLAT",AH18="UP")*9

Or shorter version

=(AF18="UP")*(AH18=UP)*12+(AF18="DOWN")*(AH18="UP")*9+(AF18="UP")*(AH18="DOW
N")*6+(AF18="DOWN")*(AH18="DOWN")*3+(AF18="FLAT")*(AH18="DOWN")*6+(AF18="FLA
T")*(AH18="UP")*9

You also can structure it (but I'm afraid it will be harder to read)

=(AF18="UP")*(AH18=UP)*12+AND((AF18="DOWN")*(AH18="UP"),(AF18="FLAT")*(AH18=
"UP"))*9+AND((AF18="UP")*(AH18="DOWN"),(AF18="FLAT")*(AH18="DOWN"))*6+(AF18=
"DOWN")*(AH18="DOWN")*3
 
A

ADE2

Hi

Can anybody help with this formula,it sort of works but i think ther
is a problem with the second AND in two of the IF'S,i keep getting
value error in some cases,when the outcome should be either "WEA
TREND" or "NOT TRENDING".




=IF(CP6>=CU6,"TRENDING",IF(CP6<0.7*CU6,"ABOUT T
TREND",IF(AND(CP6>=0.7*CU6,CP6<CU6),(AND(CP7<CP6,"WEA
TREND",IF(AND(CP6>=0.7*CU6,CP6<CU6),(AND(CP7>CP6,"NO
TRENDING"))))))))


CP6 :0.001656231
CP7: 0.000808378

CU6: 0.000523751

based on the above values the result would be "TRENDING"


Thanks again

Ad
 
A

ADE2

Hi,

I think there is a really basic error in this formula but my brain i
tired.

Can anybody help



=IF(AND(R2="CURRENCY",AA2="TUESDAY"),V9,W10,IF(AND(R2="OTHER",AA2="MONDAY"),V11,W9))

Thanks

Ad
 
A

Arvi Laanemets

Hi

=IF(LogicalExpression,FirstValue,SecondValue)

In your formula
LogicalExpression: AND(R2="CURRENCY",AA2="TUESDAY")
FirstValue: V9
SecondValue: W10
???: IF(AND(R2="OTHER",AA2="MONDAY"),V11,W9)
 
A

ADE2

Hi below is the original formula i had a problem with


=IF(AND(R2="CURRENCY",AA2="TUESDAY"),V9,W10,IF(AND(R2="OTHER",AA2="MONDAY"),V11,W9))



This is what i am tring to do

TEST 1---------TEST 2------RESULT


CURRENCY----TUESDAY------V9
-----------------other day----W10

OTHER ------MONDAY-------V11
-----------------other day----W9



The first test is does cell R2 contain the word

"CURRENCY" or "OTHER"

if outcome is "CURRENCY" then test cell AA2 for the day of the week

If outcome is "TUESDAY" then final result in formula cell would be tha
of cell V9

If day of week is NOT "TUESDAY" then final result in formula cell woul
be that of cell W10


if outcome is "OTHER" then test cell AA2 for the day of the week

If outcome is "MONDAY" then final result in formula cell would be tha
of cell V11

If day of week is NOT "MONDAY" then final result in formula cell woul
be that of cell W9

Hope that makes sense

Thanks

Ad
 
A

ADE2

=IF(AND(R2="CURRENCY",AA2="TUESDAY"),V9,IF(AND(R2="OTHER",AA2="MONDAY"),V11,IF(R2="CURRENCY",W10,IF(R2="OTHER",W9,""))))

Thanks Arv
 
A

Arvi Laanemets

Hi

=IF(R2="CURRENCY";IF(AA2="TUESDAY";V9;W10);IF(R2="OTHER";IF(AA2="MONDAY";V11
;W9);""))
 
Top