IF Function

[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

 

[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

 

[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
_ _
^¢¯^
--

 

[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

 

[Dana DeLouis]

May I suggest an alternative? You are calculating the slope of data, and
converting them into Text ("Up", "Down","Flat".) You then have to work with
this text to arrive at a numeric value.

Instead, my suggestion would be to calculate the slope like you are, but use
the SIGN function to return a +1, -1, or 0 for the slope.
If you want to "Display" Up/Down etc, then format the cell to display "UP"
for positive numbers, "Down" for negative numbers, and "Flat" for zero.
This allows one to work with a base 3 type of system.

Perhaps something like this:

=IF(B1=0,7.5,MOD(3828,6*A1+2*B1+11)+3)

Again, just an idea.

HTH
Dana DeLouis

 

[ADE2]

Thank you everybody for your time and ideas.I have now solved th
problem

Ad

 

[Dana DeLouis]

Oops! If interested, one does not need to shift by 3. Sorry.

=IF(B1=0,7.5,MOD(2533485,6*A1+2*B1+15))

Dana

 

[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

 

[ADE2]

This has solved it

 

[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

 

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

 

[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

 

[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

 

[Arvi Laanemets]

Hi

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