Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 12-16-2015, 08:30 AM
lonniepoet lonniepoet is offline Formula Help for Dividing Properly Windows 7 32bit Formula Help for Dividing Properly Office 2013
Advanced Beginner
Formula Help for Dividing Properly
 
Join Date: Dec 2015
Location: Kansas City
Posts: 41
lonniepoet is on a distinguished road
Default Formula Help for Dividing Properly

I have a spreadsheet that calculates the amount of board needed in order to run multiple orders using the same components. The yellow fields will be input fields for the user, the purple fields will be titles that do not change, and the white fields will be the displayed calculations. This is a more advanced version of a spreadsheet I did for single orders (floodcoat workup_single). I have two versions of what I am trying to do now, but I can’t get the math to work right. The spreadsheet I want to be using is “Floodcoat Workup_Combos_LGC_12-15-15.” Long story short, column U calculates how many component pieces will be required to run all units. Column F displays how many pieces can fit on one sheet to be die cut. This should be a simple math formula (=U6/F6) to get the Total Sheets REQ (Column E), but each time I do this and drag the formula down I get the #DIV error message displayed for items with input data. I would also like Column V to calculate the total MSF for each order (should be Total Sheets REQ * Sq Ft. or Column E * H) and not display errors messages as well. Lastly, V50/51 should look at the 10% marked-up total from V49 and display the correct cost by looking at the INDEX, which I have working in the other two spreadsheets. However, with the error messages it won’t work. Any help would be much appreciated and I am willing to share more information if needed.
Attached Files
File Type: xlsx Floodcoat Workup_Combos_LGC_12-15-15.xlsx (40.6 KB, 4 views)
File Type: xlsx floodcoat workup_single.xlsx (19.5 KB, 1 views)
File Type: xlsx Floodcoat Workup_Combos.xlsx (39.4 KB, 0 views)
Reply With Quote
  #2  
Old 12-16-2015, 11:08 PM
xor xor is offline Formula Help for Dividing Properly Windows 10 Formula Help for Dividing Properly Office 2013
Expert
 
Join Date: Oct 2015
Posts: 1,068
xor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of light
Default Formula Help for Dividing Properly

I am not at all sure I understand, but you might want to take a look at the attached.
Attached Files
File Type: xlsx Floodcoat Workup_Combos_LGC_12-15-15_2.xlsx (39.4 KB, 2 views)
Reply With Quote
  #3  
Old 12-17-2015, 07:52 AM
lonniepoet lonniepoet is offline Formula Help for Dividing Properly Windows 7 32bit Formula Help for Dividing Properly Office 2013
Advanced Beginner
Formula Help for Dividing Properly
 
Join Date: Dec 2015
Location: Kansas City
Posts: 41
lonniepoet is on a distinguished road
Default

Yes. That works perfectly for Column V. However, for Column E I am essentially needing it to take Column U and divide it by Column E, but I can’t figure out how to do this with an “IF” statement so that I don’t get #DIV error messages where there is not input data. See attached. Thank you for helping too.
Attached Files
File Type: xlsx Copy of Floodcoat Workup_Combos_LGC_12-15-15_2.xlsx (39.3 KB, 1 views)
Reply With Quote
  #4  
Old 12-17-2015, 10:09 AM
xor xor is offline Formula Help for Dividing Properly Windows 10 Formula Help for Dividing Properly Office 2013
Expert
 
Join Date: Oct 2015
Posts: 1,068
xor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of light
Default

Like this:
Attached Files
File Type: xlsx Floodcoat Workup_Combos_LGC_12-15-15_3.xlsx (39.4 KB, 2 views)
Reply With Quote
  #5  
Old 12-17-2015, 11:08 AM
lonniepoet lonniepoet is offline Formula Help for Dividing Properly Windows 7 32bit Formula Help for Dividing Properly Office 2013
Advanced Beginner
Formula Help for Dividing Properly
 
Join Date: Dec 2015
Location: Kansas City
Posts: 41
lonniepoet is on a distinguished road
Default

This is good, but I can't get cell V51/52 to pull-up the correct price when it references the cell above it. According to my chart on the upper right 200,000 - 500,000 should be $91, but no matter what I do I keep getting $87. Thank you.
Attached Files
File Type: xlsx Floodcoat Workup_Combos.xlsx (39.3 KB, 2 views)
Reply With Quote
  #6  
Old 12-17-2015, 11:38 AM
xor xor is offline Formula Help for Dividing Properly Windows 10 Formula Help for Dividing Properly Office 2013
Expert
 
Join Date: Oct 2015
Posts: 1,068
xor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of light
Default Formula Help for Dividing Properly

Maybe like this:
Attached Files
File Type: xlsx Floodcoat Workup_Combos_2.xlsx (39.4 KB, 2 views)
Reply With Quote
  #7  
Old 12-17-2015, 11:41 AM
lonniepoet lonniepoet is offline Formula Help for Dividing Properly Windows 7 32bit Formula Help for Dividing Properly Office 2013
Advanced Beginner
Formula Help for Dividing Properly
 
Join Date: Dec 2015
Location: Kansas City
Posts: 41
lonniepoet is on a distinguished road
Default

Exactly like that! Thank you. I think this has me covered.
Reply With Quote
  #8  
Old 12-17-2015, 11:52 AM
lonniepoet lonniepoet is offline Formula Help for Dividing Properly Windows 7 32bit Formula Help for Dividing Properly Office 2013
Advanced Beginner
Formula Help for Dividing Properly
 
Join Date: Dec 2015
Location: Kansas City
Posts: 41
lonniepoet is on a distinguished road
Default

I am kinda new to these forums. Is there something I can do to give you recognition or close out the forum?
Reply With Quote
  #9  
Old 12-17-2015, 12:27 PM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Formula Help for Dividing Properly Windows 7 64bit Formula Help for Dividing Properly Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,432
Pecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to all
Default

You can give xor rep. Click on the little scale in one of his/her posts, and click " add reputation". Add some comment if needed
Reply With Quote
Reply

Tags
formatting, formula help, index match

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
formula does not work properly msnarayanan Excel 2 08-12-2015 12:40 AM
Dividing an Excel data report into even # of rows zipp77ez### Excel 5 12-12-2014 09:00 PM
Dividing merge cell into ten equal rectangles for inputing Digit? aligahk06 Excel 0 05-12-2010 06:56 AM
Dividing line at top of document? daviddoria Word 0 10-24-2009 08:13 AM
Dividing by 100! fkaramagi Excel 2 01-30-2009 02:52 PM

Other Forums: Access Forums - Senior Forums

All times are GMT -7. The time now is 05:47 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2020, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2020 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft