First, I understand things better in A1 reference style.
So your (modified) formula:
=IF(OR(AND(RC[5]=""H"",(RC[-9]<=((RC[7]-RC[8])*R5C13+RC[8])))),""Yes"",""No"")
=IF(OR(AND(R7="H",(D7<=((T7-U7)*$M$5+U7)))),"Yes","No")
R7 = H
D7 = .707
T7 = .71
U7 = .7
M5 = .3
When I plop them into your formula:
=IF(OR(AND("H"="H",(.707<=((.71-.7)*.3+.7)))),"Yes","No")
=IF(OR(AND("H"="H",(.707<=((.01)*.3+.7)))),"Yes","No")
=IF(OR(AND("H"="H",(.707<=(.003+.7)))),"Yes","No")
=IF(OR(AND("H"="H",(.707<=.703))),"Yes","No")
=if(or(and(true,false)),"yes","no")
=if(or(false),"yes","no")
=if(false,"yes","no")
"no"
=======
I don't understand your use of or(and()).
If you want both of those requirements to be true, then use =and().
If you want either of those requirements to be true, then use =or().
Don't use both.
Yep, your correct with part of it as I just found out I changed the / to *
and now functions ok, except I can only get one of the calculations to work
as I want it.
So this part works ok:
IF(OR(AND(RC[5]=""I"",(RC[-9]>=((RC[8]-RC[7])*R5C13+RC[7])))),""Yes"",
But this doesn't:
"=IF(OR(AND(RC[5]=""H"",(RC[-9]<=((RC[7]-RC[8])*R5C13+RC[8])))),""Yes"",
I'll try to explain what I'm trying to do in an example below.
If RC[5] = "I"
and RC[-9] = 0.7030,
and RC[8] = 0.7100,
and RC[7] = 0.7000'
then if I enter 31% into R5C13, then M7 displays "Wait",
If I change it to 30% then M7 displays "Yes" and this works fine, however,
If RC[5] = "H",
and RC[-9] = 0.7070,
and RC[8] = 0.7000,
and RC[7] = 0.7100,
and I enter 30% into R5C13, I'm wanting cell M7 to display "Yes" but it
doesn't. It will only display "Yes" when the percentage is increased to 70%,
as it's only calculating from the bottom up. Hope that's clear.
Is it possible to get the % to calculate down from the higher number also
without changing the % to do so?
Please ask if you require more and think you can help.
--
Cheers
cliff18
:
Oh no! Row 5 Column 13 of course!!
Thanks Dave, but i still have a problem.
I assume I can't pull a % figure from the cell into the code. At least not
the way I'm trying to do it, using 50% in M5. It works fine if I
re-formate the M5 cell from percent back to numbers and insert 2 for
instance. That's not too hard for 50% but when I'm looking for say 23.6% it's
a bit of a problem.
Can you suggest how I may be able to do this?
--
Cheers
cliff18
--
Cheers
cliff18
:
If you want the equivalent of dividing by 2 by putting 50% in M5, then you'll
want to multiply by M5 (R5C13).
I'm not sure what you want when you have 23.6% in M5. Maybe multiply???
cliff18 wrote:
Oh no! Row 5 Column 13 of course!!
Thanks Dave, but i still have a problem.
I assume I can't pull a % figure from the cell into the code. At least not
the way I'm trying to do it, using 50% in M5. It works fine if I
re-formate the M5 cell from percent back to numbers and insert 2 for
instance. That's not too hard for 50% but when I'm looking for say 23.6% it's
a bit of a problem.
Can you suggest how I may be able to do this?
--
Cheers
cliff18
:
M5 in R1C1 reference style is R5C13
So just change the 2's that should always point to M5 to R5C13.
R5C13 is like $M$5 in A1 reference style.
cliff18 wrote:
I'm trying to alter part of a code given to me for running a macro by a
member but have had no luck seaching the help files or trial & error to get
it to work. The code works fine as it is, but I'd like to change the figure
"2" (which represents 50%) to cell "M5" which allows user input to adjust the
percentage.
So from: /2+RC[8] to: /M5+RC[8]
Working code below:
Sub ResRetr7()
Range("M7").FormulaR1C1 = _
"=IF(OR(AND(RC[5]=""H"",(RC[-9]<=((RC[7]-RC[8])/2+RC[8])))),""Yes"",IF(OR(AND(RC[5]=""I"",(RC[-9]>=((RC[8]-RC[7])/2+RC[7])))),""Yes"",IF(OR(AND(RC[5]=""H"",(RC[-9]>((RC[7]-RC[8])/2+RC[8])))),""Wait"",IF(OR(AND(RC[5]=""I"",(RC[-9]<((RC[8]-RC[7])/2+RC[7])))),""Wait"",""""))))"
End Sub
Thanks in advance.
