Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 03-04-2015, 10:32 PM
excerbated123 excerbated123 is offline Need help with mind bending calculations taken from values in multiple places Windows 8 Need help with mind bending calculations taken from values in multiple places Office 2013
Novice
Need help with mind bending calculations taken from values in multiple places
 
Join Date: Feb 2015
Posts: 10
excerbated123 is on a distinguished road
Default Need help with mind bending calculations taken from values in multiple places

I won't lie I was never too good at math. Kinda regret that now. I'm creating a project for work and need some serious help getting it done. If you're up for a challenge then read on:


So I have two sheets in the same work book. One is for writing up an estimate to give to the customer, the other is a supply form to order materials.

What I need done is for the cell on the supply form (Screenshot1) to add up the total quantity of each "Tear_Off" line item (Screenshot2). The hard part is, "Tear_Off", isn't going to always be in the same place for each estimate, so I need a formula that finds "Tear-Off" on the page then adds the quantity for each instance.

After I have that number, I then need the cell to multiply that value times the waste factor on the supply page, then divide that by the coverage per bundle (screenshot3), then divide that number by the bundles per square.



Finally, I need that number to be rounded up to the nearest bundle per square. In this instance it needs to be the next 3rd. For some shingles they come in 5 bundles per square, or 6, and so on. So I need it to be smart and figure that part out.

Any takers?

Work book attached
Attached Images
File Type: jpg ex1.jpg (160.6 KB, 4 views)
File Type: jpg ex2.jpg (168.5 KB, 4 views)
File Type: jpg ex3.jpg (163.5 KB, 4 views)
Attached Files
File Type: xlsx BetaSheet.xlsx (76.9 KB, 2 views)
Reply With Quote
  #2  
Old 03-05-2015, 07:21 AM
gebobs gebobs is offline Need help with mind bending calculations taken from values in multiple places Windows 7 64bit Need help with mind bending calculations taken from values in multiple places Office 2010 64bit
Expert
 
Join Date: Mar 2014
Location: Atlanta
Posts: 834
gebobs has a spectacular aura aboutgebobs has a spectacular aura about
Default

For the first problem, try this...

=SUMIF(Xactibate!A14:A51,"Tear_Off",Xactibate!N14: N51)

I don't see where the waste factor is.
Reply With Quote
  #3  
Old 03-05-2015, 02:18 PM
excerbated123 excerbated123 is offline Need help with mind bending calculations taken from values in multiple places Windows 8 Need help with mind bending calculations taken from values in multiple places Office 2013
Novice
Need help with mind bending calculations taken from values in multiple places
 
Join Date: Feb 2015
Posts: 10
excerbated123 is on a distinguished road
Default

Thanks thats a big part of it! Waste factor is at M5 on supply order
Reply With Quote
  #4  
Old 03-10-2015, 07:11 AM
gebobs gebobs is offline Need help with mind bending calculations taken from values in multiple places Windows 7 64bit Need help with mind bending calculations taken from values in multiple places Office 2010 64bit
Expert
 
Join Date: Mar 2014
Location: Atlanta
Posts: 834
gebobs has a spectacular aura aboutgebobs has a spectacular aura about
Default

M5 on supply order is a blank cell.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Need help with mind bending calculations taken from values in multiple places I need to add multiple values based on multiple criteria in a cell not sure what to do AUHAMM Excel 3 10-27-2014 09:11 PM
Need help with mind bending calculations taken from values in multiple places VLookup multiple values and Sum tinfanide Excel 2 09-02-2014 11:41 AM
Excel - search Multiple Values (HELP!) duskdjl Excel 4 03-28-2013 01:11 AM
Need help with mind bending calculations taken from values in multiple places Drop Down list with calculations and values winter4400 Word 7 01-16-2013 04:55 AM
Calculations using values from date picker controls Inkarnate Word 0 06-09-2010 07:16 AM

Other Forums: Access Forums - Senior Forums

All times are GMT -7. The time now is 12:32 AM.


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