Need Help Writing a Formula with Multiple IF/ OR Statements.

Hey everyone,

I've been trying to create a table for work to help with conversions and calculations and i was wondering how i should approach this formula.

I need to make a formula which will find the area and volume of a vertical or horizontal cylindrical AST and/or a rectangular AST dependent on which variables are present instead of having separate columns for the width diameter and circumference. (ex.) If the circumference of the cylinder is applied while the width and diameter are not present then calculate the volume of the cylinder using the circumference and length, or if the length is not present but the height is then to use the height instead of the length to do the calculation. It would also be helpful to find a formula to use in the same regard to find the area.

Here is an example of what my table looks like,

Attachment 275473

If any of you could please assist me with this it would be greatly appreciated.

Thanks guys,

Hi and welcome to the forum

Please do not upload a picture of your file...rather, upload a sample of your workbook, showing what data you are working with, a few samples of your expected outcome is (manually entered is ok) and how you arrived at that. (exclude sensitive info). Pictures are pretty much impossible to edit, and no-one wants to re-type your data for you Also, not all members can upload picture files (Company firewalls and stuff)

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

Data Range

	B	C	D	E	F	G	H	I	J	K
18	Berm A AST Calculations
19	AST #	Lengh (ft.)	Width (ft.)	Height (ft.)	Diameter (ft.)	Circumference (ft.)	Area formula	Area Calc result	vol formula	Volumn calc result
20	AST #1	0	0	2	0	12	=IF(AND(C20:D20),C20*D20,IF(F20,PI() * F20^2/4,G20^2/4/PI()))	11.4591559	=I20*E20	22.91831181
21	AST #2	0	3	5	0	6	=IF(AND(C21:D21),C21*D21,IF(F21,PI() * F21^2/4,G21^2/4/PI()))	2.864788976	=I21*E21	14.32394488
22	AST #3	8	8	1	0	0	=IF(AND(C22:D22),C22*D22,IF(F22,PI() * F22^2/4,G22^2/4/PI()))	64	=I22*E22	64
23	AST #4	1	2	6	0	0	=IF(AND(C23:D23),C23*D23,IF(F23,PI() * F23^2/4,G23^2/4/PI()))	2	=I23*E23	12

Warning: Avoid using merged cells!

Originally Posted by protonLeah

Data Range

	B	C	D	E	F	G	H	I	J	K
18	Berm A AST Calculations
19	AST #	Lengh (ft.)	Width (ft.)	Height (ft.)	Diameter (ft.)	Circumference (ft.)	Area formula	Area Calc result	vol formula	Volumn calc result
20	AST #1	0	0	2	0	12	=IF(AND(C20:D20),C20*D20,IF(F20,PI() * F20^2/4,G20^2/4/PI()))	11.4591559	=I20*E20	22.91831181
21	AST #2	0	3	5	0	6	=IF(AND(C21:D21),C21*D21,IF(F21,PI() * F21^2/4,G21^2/4/PI()))	2.864788976	=I21*E21	14.32394488
22	AST #3	8	8	1	0	0	=IF(AND(C22:D22),C22*D22,IF(F22,PI() * F22^2/4,G22^2/4/PI()))	64	=I22*E22	64
23	AST #4	1	2	6	0	0	=IF(AND(C23:D23),C23*D23,IF(F23,PI() * F23^2/4,G23^2/4/PI()))	2	=I23*E23	12

Warning: Avoid using merged cells!

Thanks protonLeah,

I've gotten rid of the merged cells and tried to re-write the formula this morning.

The area worked fine when i applied this technique, while the end result for the area formula came out looking like this:

=IF(AND(C16:D16),C16*D16,IF(F16,((F16/2)^2)*3.14,IF(G16,((((G16/3.14)/2)^2)*3.14))))

But, when i tried to do the same thing for the volume i got an error saying there were to many arguments for this function.
Here is how my formula reads:

=IF(AND(C16:E16,(C16*D16*E16)*0.17811,if(G16,C16,((((((G16/3.14)/2)^2)*3.14)*C16)*0.17811),if(G16,E16,((((((G16/3.14)/2)^2)*3.14)*E16)*0.17811),if(F16,C16,(((((F16/2)^2)*3.14)*C16)*0.17811),if(F16,E16,(((((F16/2)^2)*3.14)*E16)*0.17811)

not quite sure whats wrong.

any advice as to what i should change/ add?

Its always a great feeling when you solve stuff like this on your own, good job

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.