Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel 2007 - Multiple If Then or Lookup
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel 2007 - Multiple If Then or Lookup
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel 2007 - Multiple If Then or Lookup
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel 2007 - Multiple If Then or Lookup
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel 2007 - Multiple If Then or Lookup
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel 2007 - Multiple If Then or Lookup
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel 2007 - Multiple If Then or Lookup
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel 2007 - Multiple If Then or Lookup
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sum Result of lookup in excel 2007 | Excel Worksheet Functions | |||
Lookup Function for Excel 2007 | Excel Discussion (Misc queries) | |||
Excel 2007 lookup problem continuing ... | Excel Worksheet Functions | |||
Excel 2007 Lookup | Excel Worksheet Functions | |||
Excel 2007 LOOKUP Problem? | Excel Worksheet Functions |