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

palaceofreverie

New member
Joined
Nov 4, 2013
Messages
3
Reaction score
0
Points
0
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.

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

Thanks guys,
 

Attachments

  • Calculations Table.xlsx
    45.7 KB · Views: 21
I've gotten rid of the merged cells and tried to re-write the formula this morning.

The area worked fine when i applied the =IF(AND 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?

Thanks in advance.
 
I've now tried to simplify the formula and still receive the same error.

Here is how it reads currently:

=IF(AND(C16:E16),C16*D16*E16*0.17811,if(C16,H16),H16*C160.17811,if(E16,H16),H16*E16*0.17811)

Any ideas?
 
Perhaps it should be:

=IF(AND(C16>0,D16>0,E16>0),C16*D16*E16*0.17811,IF(AND(C16>0,H16>0),H16*C16*0.17811,IF(AND(E16>0,H16>0),H16*E16*0.17811,0)))

or

=0.17811*IF(AND(C16>0,D16>0,E16>0),C16*D16*E16,IF(AND(C16>0,H16>0),H16*C16,IF(AND(E16>0,H16>0),H16*E16,0)))
 
Back
Top