Hi, When i type "Yes" in Column F6, the total will be computed correctly, but
when i changed it to "No", the total will be computed with the combined
values of "Yes" + "No" typed.

E.g.
"Yes" value=200
"No" value=50
(when "Yes" is typed), total is 200 ---correct
(Now, "Yes" is replaced with "No", total is 200+50 ---should display 50
instead but the problem i faced now is that total is displaying 250.

How do i configure such that when i typed "Yes", the total will be computed
normally and when i changed to "No", it will remove the memory and show the
correct value when "No" is typed. Below is what i've typed

=IF(AND((EXACT(F6,"Yes")),H6<D6),0,I6/B1)
+IF(AND((EXACT(F6,"No")),H6<D6),I6/B1,0)

=SUBTOTAL(109,Table1[Total])

Your first formula show 0 if Yes is found, what cell or value do you
want it to show?owen.cxy;388397 Wrote:
Hi, When i type "Yes" in Column F6, the total will be computed
correctly, but
when i changed it to "No", the total will be computed with the combined
values of "Yes" + "No" typed.

E.g.
"Yes" value=200
"No" value=50
(when "Yes" is typed), total is 200 ---correct
(Now, "Yes" is replaced with "No", total is 200+50 ---should display
50
instead but the problem i faced now is that total is displaying 250.

How do i configure such that when i typed "Yes", the total will be
computed
normally and when i changed to "No", it will remove the memory and show
the
correct value when "No" is typed. Below is what i've typed

=IF(AND((EXACT(F6,"Yes")),H6<D6),0,I6/B1)
+IF(AND((EXACT(F6,"No")),H6<D6),I6/B1,0)

=SUBTOTAL(109,Table1[Total])


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=108597

It's a little hard to see what logic you want to follow from the formula you
posted, but I don't see where the Yes/No test has any significance. From
what you **wrote** in your formula, it looks like this would do the same
thing...

=I6/B1

But, if the H6<D6 part of the test is supposed to be significant, I'm think
you may just want this...

=IF(H6<D6,I6/B1,0)

If neither of these do what you want, can you explain the testing conditions
you want to implement in words for us.

--
Rick (MVP - Excel)


"owen.cxy" wrote in message
...
Hi, When i type "Yes" in Column F6, the total will be computed correctly,
but
when i changed it to "No", the total will be computed with the combined
values of "Yes" + "No" typed.

E.g.
"Yes" value=200
"No" value=50
(when "Yes" is typed), total is 200 ---correct
(Now, "Yes" is replaced with "No", total is 200+50 ---should display 50
instead but the problem i faced now is that total is displaying 250.

How do i configure such that when i typed "Yes", the total will be
computed
normally and when i changed to "No", it will remove the memory and show
the
correct value when "No" is typed. Below is what i've typed

=IF(AND((EXACT(F6,"Yes")),H6<D6),0,I6/B1)
+IF(AND((EXACT(F6,"No")),H6<D6),I6/B1,0)

=SUBTOTAL(109,Table1[Total])

Thanks Rick,

Actually what the "Yes and No" was types of options that i can choose from.
I.e. Once user selects CALL or PUT, It will compute as shown below:
=IF((EXACT(F6,"CALL")),D6-(2*G6)) + IF((EXACT(F6,"PUT")),D6+(2*G6))

And then, based on the previous condition results, it will compute
accordingly the amount of the value based on the below formula. Column "I" &
"D" are self input values:
=IF(AND((EXACT(F6,"PUT")),H6<D6),0,I6/B1)
+IF(AND((EXACT(F6,"CALL")),H6<D6),I6/B1,0)
---The problem i mentioned happens here. Hi, When i type "Put" in Column
F6, the total will be computed correctly but when i changed it to "Call", the
total will be computed with the combined values of the earlier "Put" + "Call"
when instead, it should only be the value when "Call" is typed.

Finally, based on these values. The total will be added up as shown below:
=SUBTOTAL(109,Table1[Risk to Equity])

By the way, is the way that i'm doing correct or logical or is there some
other functions that does this better?

"Rick Rothstein" wrote:

It's a little hard to see what logic you want to follow from the formula you
posted, but I don't see where the Yes/No test has any significance. From
what you **wrote** in your formula, it looks like this would do the same
thing...

=I6/B1

But, if the H6<D6 part of the test is supposed to be significant, I'm think
you may just want this...

=IF(H6<D6,I6/B1,0)

If neither of these do what you want, can you explain the testing conditions
you want to implement in words for us.

--
Rick (MVP - Excel)


"owen.cxy" wrote in message
...
Hi, When i type "Yes" in Column F6, the total will be computed correctly,
but
when i changed it to "No", the total will be computed with the combined
values of "Yes" + "No" typed.

E.g.
"Yes" value=200
"No" value=50
(when "Yes" is typed), total is 200 ---correct
(Now, "Yes" is replaced with "No", total is 200+50 ---should display 50
instead but the problem i faced now is that total is displaying 250.

How do i configure such that when i typed "Yes", the total will be
computed
normally and when i changed to "No", it will remove the memory and show
the
correct value when "No" is typed. Below is what i've typed

=IF(AND((EXACT(F6,"Yes")),H6<D6),0,I6/B1)
+IF(AND((EXACT(F6,"No")),H6<D6),I6/B1,0)

=SUBTOTAL(109,Table1[Total])

Let me ask a couple of questions for (my own) clarification.

1. You changed the "structure" of your formula from the one you posted
originally. In the original posting, each IF test in your formula had both a
TRUE and FALSE argument... in this posting's formula, you omitted the FALSE
argument (which means, since you are adding them, they will default to a
value of 0). The problem I am having now is that the (defaulted) 0 values in
your latest formula are in different locations that they were in first
formula, so I just want to be sure which formula "structure" is the correct
one. Please carefully type the formula you really want us to look at in your
response to this question (be sure to look at my second question before
doing so, though).

2. You are using the EXACT function to test for "PUT" and "CALL", but your
post's description says you are typing in "Put" and "Call"... the EXACT
function test will fail for those inputs as the function is case-sensitive,
so "PUT" does not equal "Put" for this function. So then, do you really need
the EXACT function?

3. Can any other values be put into F6 besides "PUT" and "CALL", or will
those be the only possibilities?

--
Rick (MVP - Excel)


"owen.cxy" wrote in message
...
Thanks Rick,

Actually what the "Yes and No" was types of options that i can choose
from.
I.e. Once user selects CALL or PUT, It will compute as shown below:
=IF((EXACT(F6,"CALL")),D6-(2*G6)) + IF((EXACT(F6,"PUT")),D6+(2*G6))

And then, based on the previous condition results, it will compute
accordingly the amount of the value based on the below formula. Column "I"
&
"D" are self input values:
=IF(AND((EXACT(F6,"PUT")),H6<D6),0,I6/B1)
+IF(AND((EXACT(F6,"CALL")),H6<D6),I6/B1,0)
---The problem i mentioned happens here. Hi, When i type "Put" in Column
F6, the total will be computed correctly but when i changed it to "Call",
the
total will be computed with the combined values of the earlier "Put" +
"Call"
when instead, it should only be the value when "Call" is typed.

Finally, based on these values. The total will be added up as shown below:
=SUBTOTAL(109,Table1[Risk to Equity])

By the way, is the way that i'm doing correct or logical or is there some
other functions that does this better?

"Rick Rothstein" wrote:

It's a little hard to see what logic you want to follow from the formula
you
posted, but I don't see where the Yes/No test has any significance. From
what you **wrote** in your formula, it looks like this would do the same
thing...

=I6/B1

But, if the H6<D6 part of the test is supposed to be significant, I'm
think
you may just want this...

=IF(H6<D6,I6/B1,0)

If neither of these do what you want, can you explain the testing
conditions
you want to implement in words for us.

--
Rick (MVP - Excel)


"owen.cxy" wrote in message
...
Hi, When i type "Yes" in Column F6, the total will be computed
correctly,
but
when i changed it to "No", the total will be computed with the combined
values of "Yes" + "No" typed.

E.g.
"Yes" value=200
"No" value=50
(when "Yes" is typed), total is 200 ---correct
(Now, "Yes" is replaced with "No", total is 200+50 ---should display
50
instead but the problem i faced now is that total is displaying 250.

How do i configure such that when i typed "Yes", the total will be
computed
normally and when i changed to "No", it will remove the memory and show
the
correct value when "No" is typed. Below is what i've typed

=IF(AND((EXACT(F6,"Yes")),H6<D6),0,I6/B1)
+IF(AND((EXACT(F6,"No")),H6<D6),I6/B1,0)

=SUBTOTAL(109,Table1[Total])

Hi Rick,

You are right. There are only 2 possibilities "CALL & PUT". I am also aware
about the EXACT function which is case sensitive but because i am not sure of
other availabe functions, i chose this. (If there is another function that is
not case sensitive, do advise). Pardon me for my typo.

The most recent formulae i posted today is what i exactly have now. I'm
really unsure of how to explain because i'm not sure about the implications
on other columns so i guess i will explain it as a whole.

Column "B1", "D", "G", "I" are all self input (may change as and when
needed).

D: is where i type a unknown variable (Changes as and when needed)

F: is where i type "CALL" or "PUT". There can only be these 2 input

G: is also where i type a unknown variable (Changes as and when needed)

H: is where a value will be computed based on the value of the unknown
variable in column "G" as well as whether the user types "CALL" or "PUT" in
column "F". Below is the formula.
=IF((EXACT(F6,"CALL")),D6-(2*G6)) + IF((EXACT(F6,"PUT")),D6+(2*G6))

I: is also a unknown variable (changes as and when needed)

J: is based on the values in "H" and "D" as well as whether "F" is a CALL or
PUT.
If "F is a CALL", then as long as value in "H" is lesser than "D", Column J
will display the values based on "I6/B1", if not it should be shown as 0%.
If "F is a PUT", then as long as the value in "H" is lesser than "D", Column
J will display 0%. If not, it should display the values on "I6/B1"

=IF(AND((EXACT(F6,"PUT")),H6<D6),0,I6/B1)
+IF(AND((EXACT(F6,"CALL")),H6<D6),I6/B1,0)


Hey Rick, What i've done may not make sense to you because i am not that
well verse in it. This is only what i can think of to perform what i need.
Please do teach me more efficient functions if available.

"Rick Rothstein" wrote:

Let me ask a couple of questions for (my own) clarification.

1. You changed the "structure" of your formula from the one you posted
originally. In the original posting, each IF test in your formula had both a
TRUE and FALSE argument... in this posting's formula, you omitted the FALSE
argument (which means, since you are adding them, they will default to a
value of 0). The problem I am having now is that the (defaulted) 0 values in
your latest formula are in different locations that they were in first
formula, so I just want to be sure which formula "structure" is the correct
one. Please carefully type the formula you really want us to look at in your
response to this question (be sure to look at my second question before
doing so, though).

2. You are using the EXACT function to test for "PUT" and "CALL", but your
post's description says you are typing in "Put" and "Call"... the EXACT
function test will fail for those inputs as the function is case-sensitive,
so "PUT" does not equal "Put" for this function. So then, do you really need
the EXACT function?

3. Can any other values be put into F6 besides "PUT" and "CALL", or will
those be the only possibilities?

--
Rick (MVP - Excel)


"owen.cxy" wrote in message
...
Thanks Rick,

Actually what the "Yes and No" was types of options that i can choose
from.
I.e. Once user selects CALL or PUT, It will compute as shown below:
=IF((EXACT(F6,"CALL")),D6-(2*G6)) + IF((EXACT(F6,"PUT")),D6+(2*G6))

And then, based on the previous condition results, it will compute
accordingly the amount of the value based on the below formula. Column "I"
&
"D" are self input values:
=IF(AND((EXACT(F6,"PUT")),H6<D6),0,I6/B1)
+IF(AND((EXACT(F6,"CALL")),H6<D6),I6/B1,0)
---The problem i mentioned happens here. Hi, When i type "Put" in Column
F6, the total will be computed correctly but when i changed it to "Call",
the
total will be computed with the combined values of the earlier "Put" +
"Call"
when instead, it should only be the value when "Call" is typed.

Finally, based on these values. The total will be added up as shown below:
=SUBTOTAL(109,Table1[Risk to Equity])

By the way, is the way that i'm doing correct or logical or is there some
other functions that does this better?

"Rick Rothstein" wrote:

It's a little hard to see what logic you want to follow from the formula
you
posted, but I don't see where the Yes/No test has any significance. From
what you **wrote** in your formula, it looks like this would do the same
thing...

=I6/B1

But, if the H6<D6 part of the test is supposed to be significant, I'm
think
you may just want this...

=IF(H6<D6,I6/B1,0)

If neither of these do what you want, can you explain the testing
conditions
you want to implement in words for us.

--
Rick (MVP - Excel)


"owen.cxy" wrote in message
...
Hi, When i type "Yes" in Column F6, the total will be computed
correctly,
but
when i changed it to "No", the total will be computed with the combined
values of "Yes" + "No" typed.

E.g.
"Yes" value=200
"No" value=50
(when "Yes" is typed), total is 200 ---correct
(Now, "Yes" is replaced with "No", total is 200+50 ---should display
50
instead but the problem i faced now is that total is displaying 250.

How do i configure such that when i typed "Yes", the total will be
computed
normally and when i changed to "No", it will remove the memory and show
the
correct value when "No" is typed. Below is what i've typed

=IF(AND((EXACT(F6,"Yes")),H6<D6),0,I6/B1)
+IF(AND((EXACT(F6,"No")),H6<D6),I6/B1,0)

=SUBTOTAL(109,Table1[Total])