Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
trying to set conditions for a range of cells
I'm trying to set up a formula that will do an Equation if a word and a
thickness of lumber is present in any of a range of cells. I have used cells A2:A10 to be a word, and B2:B10 to be a thickness of lumber. The formula I have now is =IF(AND(A2="poplar",B2<1),1*C2*D2/144+0.05*C2*D2/144,"") this works fine but I would like if ANY cells from A2 thru A10 said poplar and ANY cells from B2 thru B10 is less than 1 it would preform the equation I have. Right now I have to have multiple columns with the formulas entered for each type of wood and thickness. Thanks, Mike |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
trying to set conditions for a range of cells
You could use Countifs.
=IF(AND(COUNTIF(A2:A10,"poplar")0,COUNTIF(B2:B10, "<1")0),1*C2*D2/144+0.05*C2*D2/144,"") And for better control replace "poplar" and the 1 in "<1" with cell references. "mike" wrote: I'm trying to set up a formula that will do an Equation if a word and a thickness of lumber is present in any of a range of cells. I have used cells A2:A10 to be a word, and B2:B10 to be a thickness of lumber. The formula I have now is =IF(AND(A2="poplar",B2<1),1*C2*D2/144+0.05*C2*D2/144,"") this works fine but I would like if ANY cells from A2 thru A10 said poplar and ANY cells from B2 thru B10 is less than 1 it would preform the equation I have. Right now I have to have multiple columns with the formulas entered for each type of wood and thickness. Thanks, Mike |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
trying to set conditions for a range of cells
Thanks, I entered the formula and it would work for A2 and B2 but when I
enter the parameters in A5 and B5 also it wouldn't see them. "~L" wrote: You could use Countifs. =IF(AND(COUNTIF(A2:A10,"poplar")0,COUNTIF(B2:B10, "<1")0),1*C2*D2/144+0.05*C2*D2/144,"") And for better control replace "poplar" and the 1 in "<1" with cell references. "mike" wrote: I'm trying to set up a formula that will do an Equation if a word and a thickness of lumber is present in any of a range of cells. I have used cells A2:A10 to be a word, and B2:B10 to be a thickness of lumber. The formula I have now is =IF(AND(A2="poplar",B2<1),1*C2*D2/144+0.05*C2*D2/144,"") this works fine but I would like if ANY cells from A2 thru A10 said poplar and ANY cells from B2 thru B10 is less than 1 it would preform the equation I have. Right now I have to have multiple columns with the formulas entered for each type of wood and thickness. Thanks, Mike |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
trying to set conditions for a range of cells
Hi,
First you can simplify the calculation in the second half of the formula as I do below. Second, this question is not clear, let me explain, suppose your data looks like this: poplar .5 poplar 6 pine .3 pine 2.9 which lines should you be calculating? The first line meets both conditions, the second line meets the first but not the second condtion, the third line meet the second but not the first. And the fourth line meets neither. As stated you want to use lines 1, 2, and 3 in the calculation, but my suspicions are that is not really what you want. I suspect only the first line fits the condtions. Here is a formula which I think does what you probably want: =SUMPRODUCT(--(A2:A10="popular"),--(B2:B10<1),1.05*C2:C10*D2:D10/144) -- Thanks, Shane Devenshire "mike" wrote: I'm trying to set up a formula that will do an Equation if a word and a thickness of lumber is present in any of a range of cells. I have used cells A2:A10 to be a word, and B2:B10 to be a thickness of lumber. The formula I have now is =IF(AND(A2="poplar",B2<1),1*C2*D2/144+0.05*C2*D2/144,"") this works fine but I would like if ANY cells from A2 thru A10 said poplar and ANY cells from B2 thru B10 is less than 1 it would preform the equation I have. Right now I have to have multiple columns with the formulas entered for each type of wood and thickness. Thanks, Mike |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
trying to set conditions for a range of cells
If this is going to travel, make the references absolute.
=IF(AND(COUNTIF($A$2:$A$10,"poplar")0,COUNTIF($B$ 2:$B$10,"<1")0),1*C2*D2/144+0.05*C2*D2/144,"") "mike" wrote: Thanks, I entered the formula and it would work for A2 and B2 but when I enter the parameters in A5 and B5 also it wouldn't see them. "~L" wrote: You could use Countifs. =IF(AND(COUNTIF(A2:A10,"poplar")0,COUNTIF(B2:B10, "<1")0),1*C2*D2/144+0.05*C2*D2/144,"") And for better control replace "poplar" and the 1 in "<1" with cell references. "mike" wrote: I'm trying to set up a formula that will do an Equation if a word and a thickness of lumber is present in any of a range of cells. I have used cells A2:A10 to be a word, and B2:B10 to be a thickness of lumber. The formula I have now is =IF(AND(A2="poplar",B2<1),1*C2*D2/144+0.05*C2*D2/144,"") this works fine but I would like if ANY cells from A2 thru A10 said poplar and ANY cells from B2 thru B10 is less than 1 it would preform the equation I have. Right now I have to have multiple columns with the formulas entered for each type of wood and thickness. Thanks, Mike |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
trying to set conditions for a range of cells
Shane,
You are right only line 1 meets both conditions I tried to enter your formula but it only returns 0.00. I have several of the A and B cells that meet both conditions so I should be receiving a total of the those cells I would think. Any help? Mike "ShaneDevenshire" wrote: Hi, First you can simplify the calculation in the second half of the formula as I do below. Second, this question is not clear, let me explain, suppose your data looks like this: poplar .5 poplar 6 pine .3 pine 2.9 which lines should you be calculating? The first line meets both conditions, the second line meets the first but not the second condtion, the third line meet the second but not the first. And the fourth line meets neither. As stated you want to use lines 1, 2, and 3 in the calculation, but my suspicions are that is not really what you want. I suspect only the first line fits the condtions. Here is a formula which I think does what you probably want: =SUMPRODUCT(--(A2:A10="popular"),--(B2:B10<1),1.05*C2:C10*D2:D10/144) -- Thanks, Shane Devenshire "mike" wrote: I'm trying to set up a formula that will do an Equation if a word and a thickness of lumber is present in any of a range of cells. I have used cells A2:A10 to be a word, and B2:B10 to be a thickness of lumber. The formula I have now is =IF(AND(A2="poplar",B2<1),1*C2*D2/144+0.05*C2*D2/144,"") this works fine but I would like if ANY cells from A2 thru A10 said poplar and ANY cells from B2 thru B10 is less than 1 it would preform the equation I have. Right now I have to have multiple columns with the formulas entered for each type of wood and thickness. Thanks, Mike |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
trying to set conditions for a range of cells
One thing I can see is, in the formula he has "popular" but in the data
"poplar". Text mismatch would also occur if the data has trailing or leading spaces. This will cause your formula to produce 0 matches and so have a 0 total. I'd encourage you to replace the match conditions with cell references to avoid editing the formula to change your match criteria. "mike" wrote: Shane, You are right only line 1 meets both conditions I tried to enter your formula but it only returns 0.00. I have several of the A and B cells that meet both conditions so I should be receiving a total of the those cells I would think. Any help? Mike "ShaneDevenshire" wrote: Hi, First you can simplify the calculation in the second half of the formula as I do below. Second, this question is not clear, let me explain, suppose your data looks like this: poplar .5 poplar 6 pine .3 pine 2.9 which lines should you be calculating? The first line meets both conditions, the second line meets the first but not the second condtion, the third line meet the second but not the first. And the fourth line meets neither. As stated you want to use lines 1, 2, and 3 in the calculation, but my suspicions are that is not really what you want. I suspect only the first line fits the condtions. Here is a formula which I think does what you probably want: =SUMPRODUCT(--(A2:A10="popular"),--(B2:B10<1),1.05*C2:C10*D2:D10/144) -- Thanks, Shane Devenshire "mike" wrote: I'm trying to set up a formula that will do an Equation if a word and a thickness of lumber is present in any of a range of cells. I have used cells A2:A10 to be a word, and B2:B10 to be a thickness of lumber. The formula I have now is =IF(AND(A2="poplar",B2<1),1*C2*D2/144+0.05*C2*D2/144,"") this works fine but I would like if ANY cells from A2 thru A10 said poplar and ANY cells from B2 thru B10 is less than 1 it would preform the equation I have. Right now I have to have multiple columns with the formulas entered for each type of wood and thickness. Thanks, Mike |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
trying to set conditions for a range of cells
L,
Thanks I will try the formula tomorrow I hadn't noticed the popular reference. Could you expand on using a cell reference instead of the text I'm using. thanks so much for all your help. Mike "~L" wrote: One thing I can see is, in the formula he has "popular" but in the data "poplar". Text mismatch would also occur if the data has trailing or leading spaces. This will cause your formula to produce 0 matches and so have a 0 total. I'd encourage you to replace the match conditions with cell references to avoid editing the formula to change your match criteria. "mike" wrote: Shane, You are right only line 1 meets both conditions I tried to enter your formula but it only returns 0.00. I have several of the A and B cells that meet both conditions so I should be receiving a total of the those cells I would think. Any help? Mike "ShaneDevenshire" wrote: Hi, First you can simplify the calculation in the second half of the formula as I do below. Second, this question is not clear, let me explain, suppose your data looks like this: poplar .5 poplar 6 pine .3 pine 2.9 which lines should you be calculating? The first line meets both conditions, the second line meets the first but not the second condtion, the third line meet the second but not the first. And the fourth line meets neither. As stated you want to use lines 1, 2, and 3 in the calculation, but my suspicions are that is not really what you want. I suspect only the first line fits the condtions. Here is a formula which I think does what you probably want: =SUMPRODUCT(--(A2:A10="popular"),--(B2:B10<1),1.05*C2:C10*D2:D10/144) -- Thanks, Shane Devenshire "mike" wrote: I'm trying to set up a formula that will do an Equation if a word and a thickness of lumber is present in any of a range of cells. I have used cells A2:A10 to be a word, and B2:B10 to be a thickness of lumber. The formula I have now is =IF(AND(A2="poplar",B2<1),1*C2*D2/144+0.05*C2*D2/144,"") this works fine but I would like if ANY cells from A2 thru A10 said poplar and ANY cells from B2 thru B10 is less than 1 it would preform the equation I have. Right now I have to have multiple columns with the formulas entered for each type of wood and thickness. Thanks, Mike |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
trying to set conditions for a range of cells
Sure,
In a cell somewhere out of your data range (in the examples we've been using so far, row 12 or colum F would work) put Poplar in a cell by itself and the number 1 in a cell by itself. Then change the formula to (putting the lookup values in columns F and G with column headers): =SUMPRODUCT(--($A$2:$A$10=F2),--($B$2:$B$10<G2),1.05*C2:C10*D2:D10/144) Then, when you need to evaluate by different criteria, either add to that list (populating F3 and G3, F4 and G4, etc) or change the values in F2 and G2 to your new criteria. Unless you're changing from a 'less than X inches' critera to a 'Greater than X inches' Criteria, you won't have to edit the formula. "mike" wrote: L, Thanks I will try the formula tomorrow I hadn't noticed the popular reference. Could you expand on using a cell reference instead of the text I'm using. thanks so much for all your help. Mike "~L" wrote: One thing I can see is, in the formula he has "popular" but in the data "poplar". Text mismatch would also occur if the data has trailing or leading spaces. This will cause your formula to produce 0 matches and so have a 0 total. I'd encourage you to replace the match conditions with cell references to avoid editing the formula to change your match criteria. "mike" wrote: Shane, You are right only line 1 meets both conditions I tried to enter your formula but it only returns 0.00. I have several of the A and B cells that meet both conditions so I should be receiving a total of the those cells I would think. Any help? Mike "ShaneDevenshire" wrote: Hi, First you can simplify the calculation in the second half of the formula as I do below. Second, this question is not clear, let me explain, suppose your data looks like this: poplar .5 poplar 6 pine .3 pine 2.9 which lines should you be calculating? The first line meets both conditions, the second line meets the first but not the second condtion, the third line meet the second but not the first. And the fourth line meets neither. As stated you want to use lines 1, 2, and 3 in the calculation, but my suspicions are that is not really what you want. I suspect only the first line fits the condtions. Here is a formula which I think does what you probably want: =SUMPRODUCT(--(A2:A10="popular"),--(B2:B10<1),1.05*C2:C10*D2:D10/144) -- Thanks, Shane Devenshire "mike" wrote: I'm trying to set up a formula that will do an Equation if a word and a thickness of lumber is present in any of a range of cells. I have used cells A2:A10 to be a word, and B2:B10 to be a thickness of lumber. The formula I have now is =IF(AND(A2="poplar",B2<1),1*C2*D2/144+0.05*C2*D2/144,"") this works fine but I would like if ANY cells from A2 thru A10 said poplar and ANY cells from B2 thru B10 is less than 1 it would preform the equation I have. Right now I have to have multiple columns with the formulas entered for each type of wood and thickness. Thanks, Mike |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sum range if 2 conditions are met | Excel Worksheet Functions | |||
If 2 conditions exist in a range of cells | New Users to Excel | |||
shade cells based on conditions - i have more than 3 conditions | Excel Worksheet Functions | |||
counting cells in a data range that meet 3 specific conditions | Excel Discussion (Misc queries) | |||
Add cells from a range based on 2 conditions from 2 other ranges | Excel Worksheet Functions |