Excel 2007 - Multiple If Then or Lookup

[SpecialK]

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

 

[Jacob Skaria]

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

 

[Charabeuh]

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]

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 K55 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

 

[Charabeuh]

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]

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.

 

[SpecialK]

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

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

 

[Charabeuh]

Ok !

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

Bye