# Help Creating Quoting Formula - Please and Thank You

I am trying to build on a quote spreadsheet I made for a moving company. I am about to obviously add numbers to a field and based on those numbers I have a formula to add them together to get a total price. This is Trip Charge + # of Men and # of Trucks. What I want to create or see if its possible to create is a predictive or suggestion based spreadsheet.

Where someone who has never done moving could enter say square-footage from customer and have a suggested # of Men and # of Trucks set up and form I already created could suggest price.

Example: 2,200 SQFT home typically would take 4 men 1 Truck 6-7hours, I want to be able to type in 2,200sqft and it auto populates 4 men and 1 truck in cells, but I also want to be able to change cells incase for example I need to switch it to two trucks. I believe a table below would help but I don't know functions to connect it all.

SQFT | # of Floors | # of Men | # of Trucks | # of Hours
500 > 1000 1 2 1 2
500 > 1000 2>= 2 1 2
1000 > 1250 1 2 1 3
1000 > 1250 2>= 3 1 3
1250 > 1500 1 3 1 4
1250 > 1500 2>= 3 1 4
1500 > 2000 1 3 1 5
1500 > 2000 2>= 4 1 5
2000 > 2500 1 4 1 6
2000 > 2500 2>= 4 1 7
2500 > 3000 1 4 2 7
2500 > 3000 2>= 4 2 8
3000 > 3500 1 6 3 8
3000 > 3500 2>= 6 3 9
3500 + On-Site Quote

Basically That would be table and suggested set ups. When the number of men, trucks and hours are in proper cells it would have a total price. Each man is \$35 each truck is \$30

4 Man 2 Truck Job is \$200 per hour + Trip Charge. so this would be for 7 hours = \$1,400 + Trip Charge

Thanks For any help with lay out or formulas guys.

### I'm almost sure there is an

I'm almost sure there is an easier way to do this. But I have provided a solution to your request using a simple if statement. As this is my first input to this site, I have no idea how to share the file I made. But I added the information you gave in your message as a list with index numbers (serial numbering), then the If statement would choose the right row to bring back data from based on submitted SQFT and number of floors.

Index SQFT SQFT-Min SQFT-Max # of Floors # of Men # of Trucks # of Hours
1 500 > 1000 1 2 1 2 500 1000 1 2 1 2
2 500 > 1000 2>= 2 1 2 500 1000 2 2 1 2
3 1000 > 1250 1 2 1 3 1001 1250 1 2 1 3
4 1000 > 1250 2>= 3 1 3 1001 1250 2 3 1 3
5 1250 > 1500 1 3 1 4 1251 1500 1 3 1 4
6 1250 > 1500 2>= 3 1 4 1251 1500 2 3 1 4
7 1500 > 2000 1 3 1 5 1501 2000 1 3 1 5
8 1500 > 2000 2>= 4 1 5 1501 2000 2 4 1 5
9 2000 > 2500 1 4 1 6 2001 2500 1 4 1 6
10 2000 > 2500 2>= 4 1 7 2001 2500 2 4 1 7
11 2500 > 3000 1 4 2 7 2501 3000 1 4 2 7
12 2500 > 3000 2>= 4 2 8 2501 3000 2 4 2 8
13 3000 > 3500 1 6 3 8 3001 3500 1 6 3 8
14 3000 > 3500 2>= 6 3 9 3001 3500 2 6 3 9
15 3500 + On-Site Quote 3501 9999999 "On-Site Quote)

Default Trip Charge \$200

Index=IF(C26<\$D\$5,IF(D26=2,\$A\$6,\$A\$5),IF(C26<\$D\$7,IF(D26=1,\$A\$7,\$A\$8),IF(C26<\$D\$9,IF(D26=1,\$A\$9,\$A\$10),IF(C26<\$D\$11,IF(D26=1,\$A\$11,\$A\$12),IF(C26<\$D\$13,IF(D26=1,\$A\$13,\$A\$14),IF(C26<\$D\$15,IF(D26=1,\$A\$15,\$A\$16),IF(C26<\$D\$17,IF(D26=1,\$A\$17,\$A\$18),"On-Site Quote")))))))

New assignment SQFT # of Floors Index # of Men # of Trucks # of Hours Trip charge Cost
Test new assignment 2600 1 11 4 2 7 \$200 \$1,600

Have a good day.