Cant get excel forumula to work

I

Ibonic

New Member
Joined
Jun 19, 2011
Messages
30
Hi Im a bit of novice when it comes to excel and im trying to get this forumula to work without success, its for a spreadsheet i use at work.

=IF(A28="Base Field",0,IF(A28="180 Flam (97 sf)",ROUNDUP((C5+F5)*1.1/97,0),IF(A28="630 Soprafix (95 sf)",0,IF(A28="810 Colvent (118 sf)",0,IF(A28="Colph 1500 (193 sf)",0,IF(A28="Elastph PS (147 sf)",ROUNDUP((C5+F5)*1.1/294,0),IF(A28="Sopraply 520 (97 sf)",ROUNDUP((C5+F5)*1.1/97,0),IF(A30="Base Detail",0,IF(A30=" SFFS (147 sf)",,IF(A30="SFS (97 sf)",0,IF(A30="180 Flam (97 sf)",ROUNDUP((C6)*1.2/97,0),"")))))))))))
Im trying to add up the totals from two different cells into one cell

Any help would be greatly appreciated.

Thank you
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Sort by date Sort by votes
What two cells are you adding?

I am wondering if removing some of the IF() logical tests that would evaluate to 0 could help streamline the formula??

<TABLE style="WIDTH: 971pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=1294><COLGROUP><COL style="WIDTH: 971pt; mso-width-source: userset; mso-width-alt: 23661" width=1294><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #d7e4bc; WIDTH: 971pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 width=1294>=IF(A28="180 Flam (97 sf)",ROUNDUP((C5+F5)*1.1/97,0),IF(A28="Elastph PS (147 sf)",ROUNDUP((C5+F5)*1.1/294,0),IF(A28="Sopraply 520 (97 sf)",ROUNDUP((C5+F5)*1.1/97,0),IF(A30="180 Flam (97 sf)",ROUNDUP((C6)*1.2/97,0),0))))


</TD></TR></TBODY></TABLE>


-Jeff
 
Upvote 0
repairman615 said:
What two cells are you adding?

I am wondering if removing some of the IF() logical tests that would evaluate to 0 could help streamline the formula??

<TABLE style="WIDTH: 971pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=1294><COLGROUP><COL style="WIDTH: 971pt; mso-width-source: userset; mso-width-alt: 23661" width=1294><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #d7e4bc; WIDTH: 971pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 width=1294>=IF(A28="180 Flam (97 sf)",ROUNDUP((C5+F5)*1.1/97,0),IF(A28="Elastph PS (147 sf)",ROUNDUP((C5+F5)*1.1/294,0),IF(A28="Sopraply 520 (97 sf)",ROUNDUP((C5+F5)*1.1/97,0),IF(A30="180 Flam (97 sf)",ROUNDUP((C6)*1.2/97,0),0))))



</TD></TR></TBODY></TABLE>


-Jeff
Click to expand...

I guess its more than from 2 cells im adding up, it was hard for me to explain, but when a certain product is chose on the spreadsheet, the idea is that it would automatically add up in another box
 
Upvote 0
Would bbe better to upload to 4shared.com or other file share site. This way other forum members could also contribute.

www.4shared.com

-Jeff

Set your data up in a table. have a list of the items in first column, then in the next column, have what you would like to return.

Based on what is choosen in cell C1, the formula in A1 will return the result, which is a formula in the table.

Excel 2007<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #d7e4bc">11</TD><TD style="TEXT-ALIGN: right"></TD><TD style="BACKGROUND-COLOR: #d99795">Dog</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="TEXT-ALIGN: right"></TD><TD>List</TD><TD>Result</TD><TD>Var1</TD><TD>Var2</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="TEXT-ALIGN: right"></TD><TD>Dog</TD><TD style="TEXT-ALIGN: right">11</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">10</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD style="TEXT-ALIGN: right"></TD><TD>Cat</TD><TD style="TEXT-ALIGN: right">13</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">11</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD style="TEXT-ALIGN: right"></TD><TD>Horse</TD><TD style="TEXT-ALIGN: right">15</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">12</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD style="TEXT-ALIGN: right"></TD><TD>Planet</TD><TD style="TEXT-ALIGN: right">17</TD><TD style="TEXT-ALIGN: right">4</TD><TD style="TEXT-ALIGN: right">13</TD></TR></TBODY></TABLE>
Sheet1​


<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>A1</TH><TD style="TEXT-ALIGN: left">=VLOOKUP(C1,Table1,2,FALSE)</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>C4</TH><TD style="TEXT-ALIGN: left">=D4+E4</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>C5</TH><TD style="TEXT-ALIGN: left">=D5+E5</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>C6</TH><TD style="TEXT-ALIGN: left">=D6+E6</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>C7</TH><TD style="TEXT-ALIGN: left">=D7+E7</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
 
Upvote 0
repairman615 said:
Would bbe better to upload to 4shared.com or other file share site. This way other forum members could also contribute.

www.4shared.com

-Jeff

Set your data up in a table. have a list of the items in first column, then in the next column, have what you would like to return.

Based on what is choosen in cell C1, the formula in A1 will return the result, which is a formula in the table.

Excel 2007<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #d7e4bc">11</TD><TD style="TEXT-ALIGN: right"></TD><TD style="BACKGROUND-COLOR: #d99795">Dog</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="TEXT-ALIGN: right"></TD><TD>List</TD><TD>Result</TD><TD>Var1</TD><TD>Var2</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="TEXT-ALIGN: right"></TD><TD>Dog</TD><TD style="TEXT-ALIGN: right">11</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">10</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD style="TEXT-ALIGN: right"></TD><TD>Cat</TD><TD style="TEXT-ALIGN: right">13</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">11</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD style="TEXT-ALIGN: right"></TD><TD>Horse</TD><TD style="TEXT-ALIGN: right">15</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">12</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD style="TEXT-ALIGN: right"></TD><TD>Planet</TD><TD style="TEXT-ALIGN: right">17</TD><TD style="TEXT-ALIGN: right">4</TD><TD style="TEXT-ALIGN: right">13</TD></TR></TBODY></TABLE>
Sheet1​


<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>A1</TH><TD style="TEXT-ALIGN: left">=VLOOKUP(C1,Table1,2,FALSE)</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>C4</TH><TD style="TEXT-ALIGN: left">=D4+E4</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>C5</TH><TD style="TEXT-ALIGN: left">=D5+E5</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>C6</TH><TD style="TEXT-ALIGN: left">=D6+E6</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>C7</TH><TD style="TEXT-ALIGN: left">=D7+E7</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
Click to expand...

Im not sure how this would apply to my spreadsheet?
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,224,616
Messages
6,179,911
Members
452,949
Latest member
beartooth91

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top