Excel Rookie Needs Help

[JDRan]

I've created a formula for figuring out the capacity of a section of oil pipeline in US barrels using the formula for area, pi*radius squared*length. The diameter of the pipe is in Column E and is displayed there in inches. The length of the pipe is in Column F and is displayed in feet. My version of excel doesn't recognize the difference between the different units of measure, it only sees the numerical value entered in each cell in Column E. So i created a table on a second sheet where I converted inches to decimals of a foot. There are 10 rows in this table showing decimals of a foot for the different sizes of pipe we use. The lengths of each section of pipeline varies in every cell of Column F. The formula I used to get the area to fill into Column D is:

=PI()*(Tables!I8^2)*Commissioning!F7*0.17811

Tables!I8^2 is from the table on the second sheet and Commissioning!F7 is from the main sheet. The last piece is the conversion from gallons to US barrels of oil. I can program this in manually into each cell and change the "I#" and the "F#" depending on the diameter and length for that given pipeline.

What I would like to do is be able to have the Capacity cell in Column D automatically figure when I enter the values into Column E "Diameter" and Column F "Length." I was going to create a rule for each of the 10 sizes of diameter from the table I created on the second page and use "$F1" in the Commissioning spot. I put this formula in to Conditional Formatting for all the columns involved. I've tried this several ways and none seem to work. I've used the "IF" and "AND" and I didn't get any errors but it doesn't give me the result in Column D. This is what I entered:

=IF(AND($C1="Oil",$E1="20"),$D1=PI()*(Tables!I10^2)*$F1*0.17811)

Anyone have an idea of how to make this work? Sorry for the long first post.

 

[sandy666]

try this AND($C1="Oil",$E1=20)

 

[jtakw]

Hi,

Not suggesting this correction to your formula will give you correct results, as I don't fully understand what you're doing, but try this:

=IF(AND($C1="Oil",$E1=20),PI()*(Tables!I10^2)*$F1*0.17811,"value if false")

 

[fhqwgads]

=IF(AND($C1="Oil",$E1="20"),$D1=PI()*(Tables!I10^2)*$F1*0.17811)

are you putting that as your conditional formatting rule?

try

=AND($C1="Oil",$E1="20",$D1=PI()*(Tables!I10^2)*$F1*0.17811)

 

[jtakw]

Ok, if that's supposed to be a CF formula as mentioned above, you'll need to do this:

=AND($C1="Oil",$E1=20,$D1=PI()*(Tables!I10^2)*$F1*0.17811)

No IF, and remove the quotes around 20

 

[sandy666]

I lost $F1

so try: AND($C1="Oil",$E1=20,$F1<>"")

 

[sandy666]

correction:
=AND($C1="Oil",$E1=20,$F1<>0,$D1=PI()*(Tables!I10^2)*$F1*0.17811)