Microsoft Office Forums Need help with mind bending calculations taken from values in multiple places
 Register FAQ Search Today's Posts Mark Forums Read

#1
03-04-2015, 10:32 PM
 excerbated123 Windows 8 Office 2013 Novice Join Date: Feb 2015 Posts: 10
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
 ex1.jpg (160.6 KB, 4 views) ex2.jpg (168.5 KB, 4 views) ex3.jpg (163.5 KB, 4 views)
Attached Files
 BetaSheet.xlsx (76.9 KB, 2 views)
#2
03-05-2015, 07:21 AM
 gebobs Windows 7 64bit Office 2010 64bit Expert Join Date: Mar 2014 Location: Atlanta Posts: 834

For the first problem, try this...

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

I don't see where the waste factor is.
#3
03-05-2015, 02:18 PM
 excerbated123 Windows 8 Office 2013 Novice Join Date: Feb 2015 Posts: 10

Thanks thats a big part of it! Waste factor is at M5 on supply order
#4
03-10-2015, 07:11 AM
 gebobs Windows 7 64bit Office 2010 64bit Expert Join Date: Mar 2014 Location: Atlanta Posts: 834

M5 on supply order is a blank cell.

 Thread Tools Display Modes Linear Mode

 Similar Threads Thread Thread Starter Forum Replies Last Post AUHAMM Excel 3 10-27-2014 09:11 PM tinfanide Excel 2 09-02-2014 11:41 AM duskdjl Excel 4 03-28-2013 01:11 AM winter4400 Word 7 01-16-2013 04:55 AM 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.

 -- Default Style -- Lightweight -- New Mobile Contact Us - Privacy Statement - Top