I'm trying to check cells K5 or K6 or K7 or K8 or K9 for the presence of the
letter "x" and if "True" to multiply cell H5 by the appropriate percentage
and then add cell I5 to the total. Additionally, I need to simultaneously
check cell K10 for the letter "x" and if TRUE then the previous total needs
to be multiplied by .95

Hi "SpecialK"

Try the below formula ...with 10%. Adjust the % as required.

=IF(COUNTIF(K5:K9,"x")=0,0,((H5*0.1)+I5)*IF(K10="X ",0.95,1))

If this post helps click Yes
---------------
Jacob Skaria


"SpecialK" wrote:

I'm trying to check cells K5 or K6 or K7 or K8 or K9 for the presence of the
letter "x" and if "True" to multiply cell H5 by the appropriate percentage
and then add cell I5 to the total. Additionally, I need to simultaneously
check cell K10 for the letter "x" and if TRUE then the previous total needs
to be multiplied by .95

Hi

I suppose that for each cells in K5:K9 there is the appropriate percentage
in the corresponding cell of L5:L9

=IF(COUNTIF(K5:K9,"x")=1,(I5+H5*SUMPRODUCT( (K5:K9="x")*(L5:L9))) *
(IF(K10="x",0.95,1)),"no percentage ")





"SpecialK" a écrit dans le message de
...
I'm trying to check cells K5 or K6 or K7 or K8 or K9 for the presence of
the
letter "x" and if "True" to multiply cell H5 by the appropriate percentage
and then add cell I5 to the total. Additionally, I need to simultaneously
check cell K10 for the letter "x" and if TRUE then the previous total
needs
to be multiplied by .95

Hi,
Thanks for trying to help me...this is driving me nuts.
I think you're on the right track...however, i was tired and i screwed up
the description of my cells so your solution doesn't work because I gave
everyone incorrect information. Please forgive me...
This time it will be accurate...

I have an "X" in K5 or L5 or M5 or N5 or O5 * P5 is for a secondary
calculation
That "x" in the particular cell corresponds to a particular percentage in
U5:Z5
The appropriate percentage from U5:Z5 then must be multiplied by the value
in cell i5 and then add the value of cell J5 to the amount.
Finally, IF there is an "x" in P5 then the previously calculated total must
be multiplied by 95%.
FYI - My K5:P5 columns are fixed in terms of placement on the form but if a
lookup or reference table for U5:Z5 has to be created then I can put that off
to the side of the form.

Thanks again for whatever help you can provide. If this doesn't work, is
there anyway I can send you the attachment so you can see visually what I'm
trying to get done here?

Kevin

Your "x" are in cells K5 to O5 (five cells)
Your percentages are in cells U5 to Z5 (six cells)
I supposed that your percentages are in cells
U5 to Y5 and not in cells U5 to Z5.

The formula is (I guess)
=IF(COUNTIF(K5:O5,"x")=1,(J5+I5*SUMPRODUCT( (K5:O5="x")*(U5:Y5))) *
(IF(P5="x",0.95,1)),"no percentage ")
-----------------------------------------------------------------

if COUNTIF(K5:O5),"x")=1 then we calculate your formula otherwise we return
the string "no percentage"
(we suppose that one and only one "x" must be present )

your formula:
sumproduct returns the product (term by term) of the two arrays:
(K5:O5="x") and (U5:Y5)

for instance if M5 is "x" then
the first array is {K5="x",L5="x",M5="x",N5="x",O5="x"}
= {false,false,true,false,false}

and the second array is {rateU5,rateV5,rateW5,rateX5,rateY5}

the sumproduct is :
sum({false,false,true,false,false}* {rateU5,rateV5,rateW5,rateX5,rateZ5})
then sum({0,0,rateW5,0,0}) = rateW5
-false is converted to 0 when multiplied with a number-
-true is converted to 1 when multiplied with a number-

Then rateW5 is multiplied by I5
then we add J5

and then we multiply with the result of:
IF(P5="x",0.95,1)

hope it will help you.








"SpecialK" a écrit dans le message de
...
Hi,
Thanks for trying to help me...this is driving me nuts.
I think you're on the right track...however, i was tired and i screwed up
the description of my cells so your solution doesn't work because I gave
everyone incorrect information. Please forgive me...
This time it will be accurate...

I have an "X" in K5 or L5 or M5 or N5 or O5 * P5 is for a secondary
calculation
That "x" in the particular cell corresponds to a particular percentage in
U5:Z5
The appropriate percentage from U5:Z5 then must be multiplied by the value
in cell i5 and then add the value of cell J5 to the amount.
Finally, IF there is an "x" in P5 then the previously calculated total
must
be multiplied by 95%.
FYI - My K5:P5 columns are fixed in terms of placement on the form but if
a
lookup or reference table for U5:Z5 has to be created then I can put that
off
to the side of the form.

Thanks again for whatever help you can provide. If this doesn't work, is
there anyway I can send you the attachment so you can see visually what
I'm
trying to get done here?

Kevin

Firstly...Thank you Jacob for your help
Secondly...Charabeuh...It Worked!!!!!!! Fantastic!!!!!!
I don't know how you guys can come up with the formulas from the little bit
of information people give you. You guys are just fantastic. Thank You so
much.

Charabeuh...one last tweak if you would...
how can I keep the formula referencing U5:Y5 constant when I drag the
formula cell down the sheet to copy it 29 more times? The K5:O5 changes
correctly but I need the U5:Y5 to remain constant...how can I do that?
Thanks.



"Charabeuh" wrote:

Your "x" are in cells K5 to O5 (five cells)
Your percentages are in cells U5 to Z5 (six cells)
I supposed that your percentages are in cells
U5 to Y5 and not in cells U5 to Z5.

The formula is (I guess)
=IF(COUNTIF(K5:O5,"x")=1,(J5+I5*SUMPRODUCT( (K5:O5="x")*(U5:Y5))) *
(IF(P5="x",0.95,1)),"no percentage ")
-----------------------------------------------------------------

if COUNTIF(K5:O5),"x")=1 then we calculate your formula otherwise we return
the string "no percentage"
(we suppose that one and only one "x" must be present )

your formula:
sumproduct returns the product (term by term) of the two arrays:
(K5:O5="x") and (U5:Y5)

for instance if M5 is "x" then
the first array is {K5="x",L5="x",M5="x",N5="x",O5="x"}
= {false,false,true,false,false}

and the second array is {rateU5,rateV5,rateW5,rateX5,rateY5}

the sumproduct is :
sum({false,false,true,false,false}* {rateU5,rateV5,rateW5,rateX5,rateZ5})
then sum({0,0,rateW5,0,0}) = rateW5
-false is converted to 0 when multiplied with a number-
-true is converted to 1 when multiplied with a number-

Then rateW5 is multiplied by I5
then we add J5

and then we multiply with the result of:
IF(P5="x",0.95,1)

hope it will help you.








"SpecialK" a écrit dans le message de
...
Hi,
Thanks for trying to help me...this is driving me nuts.
I think you're on the right track...however, i was tired and i screwed up
the description of my cells so your solution doesn't work because I gave
everyone incorrect information. Please forgive me...
This time it will be accurate...

I have an "X" in K5 or L5 or M5 or N5 or O5 * P5 is for a secondary
calculation
That "x" in the particular cell corresponds to a particular percentage in
U5:Z5
The appropriate percentage from U5:Z5 then must be multiplied by the value
in cell i5 and then add the value of cell J5 to the amount.
Finally, IF there is an "x" in P5 then the previously calculated total
must
be multiplied by 95%.
FYI - My K5:P5 columns are fixed in terms of placement on the form but if
a
lookup or reference table for U5:Z5 has to be created then I can put that
off
to the side of the form.

Thanks again for whatever help you can provide. If this doesn't work, is
there anyway I can send you the attachment so you can see visually what
I'm
trying to get done here?

Kevin

Thanks for your help Jacob...I appreciate it.


"Jacob Skaria" wrote:

Hi "SpecialK"

Try the below formula ...with 10%. Adjust the % as required.

=IF(COUNTIF(K5:K9,"x")=0,0,((H5*0.1)+I5)*IF(K10="X ",0.95,1))

If this post helps click Yes
---------------
Jacob Skaria


"SpecialK" wrote:

I'm trying to check cells K5 or K6 or K7 or K8 or K9 for the presence of the
letter "x" and if "True" to multiply cell H5 by the appropriate percentage
and then add cell I5 to the total. Additionally, I need to simultaneously
check cell K10 for the letter "x" and if TRUE then the previous total needs
to be multiplied by .95

Ok !

to remain constant U5:Y5, replace in the formula :
U5:Y5 with $U$5:$Y$5.

Bye




"SpecialK" a écrit dans le message de
...
Firstly...Thank you Jacob for your help
Secondly...Charabeuh...It Worked!!!!!!! Fantastic!!!!!!
I don't know how you guys can come up with the formulas from the little
bit
of information people give you. You guys are just fantastic. Thank You
so
much.

Charabeuh...one last tweak if you would...
how can I keep the formula referencing U5:Y5 constant when I drag the
formula cell down the sheet to copy it 29 more times? The K5:O5 changes
correctly but I need the U5:Y5 to remain constant...how can I do that?
Thanks.



"Charabeuh" wrote:

Your "x" are in cells K5 to O5 (five cells)
Your percentages are in cells U5 to Z5 (six cells)
I supposed that your percentages are in cells
U5 to Y5 and not in cells U5 to Z5.

The formula is (I guess)
=IF(COUNTIF(K5:O5,"x")=1,(J5+I5*SUMPRODUCT( (K5:O5="x")*(U5:Y5))) *
(IF(P5="x",0.95,1)),"no percentage ")
-----------------------------------------------------------------

if COUNTIF(K5:O5),"x")=1 then we calculate your formula otherwise we
return
the string "no percentage"
(we suppose that one and only one "x" must be present )

your formula:
sumproduct returns the product (term by term) of the two arrays:
(K5:O5="x") and (U5:Y5)

for instance if M5 is "x" then
the first array is {K5="x",L5="x",M5="x",N5="x",O5="x"}
= {false,false,true,false,false}

and the second array is {rateU5,rateV5,rateW5,rateX5,rateY5}

the sumproduct is :
sum({false,false,true,false,false}* {rateU5,rateV5,rateW5,rateX5,rateZ5})
then sum({0,0,rateW5,0,0}) = rateW5
-false is converted to 0 when multiplied with a number-
-true is converted to 1 when multiplied with a number-

Then rateW5 is multiplied by I5
then we add J5

and then we multiply with the result of:
IF(P5="x",0.95,1)

hope it will help you.








"SpecialK" a écrit dans le message
de
...
Hi,
Thanks for trying to help me...this is driving me nuts.
I think you're on the right track...however, i was tired and i screwed
up
the description of my cells so your solution doesn't work because I
gave
everyone incorrect information. Please forgive me...
This time it will be accurate...

I have an "X" in K5 or L5 or M5 or N5 or O5 * P5 is for a
secondary
calculation
That "x" in the particular cell corresponds to a particular percentage
in
U5:Z5
The appropriate percentage from U5:Z5 then must be multiplied by the
value
in cell i5 and then add the value of cell J5 to the amount.
Finally, IF there is an "x" in P5 then the previously calculated total
must
be multiplied by 95%.
FYI - My K5:P5 columns are fixed in terms of placement on the form but
if
a
lookup or reference table for U5:Z5 has to be created then I can put
that
off
to the side of the form.

Thanks again for whatever help you can provide. If this doesn't work,
is
there anyway I can send you the attachment so you can see visually what
I'm
trying to get done here?

Kevin